Nach Datum suchen und wert von nächste spalte ausgeben, brauche Hilfe?
In spalte A steht "tt.mm.yyyy hh.mm.ss" und rechts davon ist ein wert den ich brauche also ausgelesen werden soll, das such Kriterium soll nur tt.mm.yy sein und mir die rechte Zelle ausgeben. Versucht habe ich es mit =vlookup(DATEVALUE("25.02.2016"); $A$37:$B$999;2) das, funktioniert nicht 100%. Wenn ich diese Formel in die nächste Zeile eingebe wird mir der gleiche wert von 25.02.2016 ausgegeben obwohl, in der Formel nach dem 26.02.2016 sucht, aber kein wert rausgibt weil, kein wert eingetragen ist.
SpalteA--------------------------Spalte B
25.02.2016 22:00:15 -------3,26
der wert aus spalte B wird ausgeben
Hoffe ich habe es verständlich ausgedrückt.
2 Antworten
die einfachste Möglichkeit ist, Du fügst vor der Datum+Zeitspalte eine weitere ein mit der Formel: =Ganzzahl(rechts daneben) und formatierst das als einfaches Datum. Dann sollte die Suche mit Deiner Formel (nur jetzt Spalte 3 anstatt 2 und -nicht vergessen: 4.Argument 0 oder FALSCH-) funktionieren.
Ansonsten gäbe es noch die Möglichkeit, die Spalte mit einer Matrixformel auf Ganzzahl abfragbar zu machen, müsste in etwa so aussehen:
{=vlookup(DATEVALUE("25.02.2016");integer($A$37:$B$999);2;0)}
(die letzte ;0) steht für exakte Übereinstimmung wie von Dir gewünscht). Ich denke, das geht, probier das aber heute nicht mehr aus.
Prüfe auch noch, ob xl das deutsche Datumsformat im englischen Datevalue() überhaupt "versteht" (überstreiche mal das DATEVALUE("25.02.2016") mit dem Cursor und drücke F9, ob da eine Datumszahl erscheint (Zelle mit esc wieder verlassen)!
Achtung: Dies ist eine Matrixformel! Die geschweiften {Klammern} NICHT miteingeben, sondern die Formel anstatt mit einfachem Enter mit der Kombination (gleichzeitig) Strg+Shift+Enter abschließen (nach jeder Neubearbeitung wieder!). Die {Klammern} entstehen dann automatisch.
Schreibtechnisch nimmst Du am besten die rechte Strg- und Shifttaste, dann gehts mit einer Hand und lässt sich auch leichter merken! - - -
Noch ein Tipp: Wenn Du in der Bearbeitungszeile einen selbständigen Formelteil markierst und F9 drückst, erhältst Du ein(e) Ergebnis{liste}. Allerdings dann die Zelle mit Esc verlassen (oder rückgängig machen), Enter würde das gezeigte Ergebnis festschreiben! Vorläufig kurze Bereiche festlegen! (mindestens 2 Zellen markieren, Strg+h, ... ersetzen durch ...) --
Die Crux beim SVerweis ist, dass Du einen Bereich angeben musst, bei dem mit Ganzzahl nichts anzufangen ist. Vergleich dagegen nennt nur EINE Spalte, bei der Ganzzahl problemlos geht. (schon wieder / noch ein Grund gegen den SVERWEIS!)
Das einzige: die Zellen aus der Vergleichsspalte und der Holspalte müssen in der gleichen Zeile beginnen, sonst sind Korrekturglieder erforderlich!
gteht übrigens auch ohne Matrixformel-EINGABE so:
=index(B$1:B$999;Summenprodukt((A$1:A$999>="Datum"*1)*(A$1:A$999<"Datum"+1)*Zeile(A$1:A$999)))
Da wird das Intervall zwischen Datum und nächstem Tag abgefragt. Es darf natürlich nur EINEN Treffer geben! (sonst kompliziertere Korrektur nötig)
ohne Matrixformel-Eingabe gehts mit der Quasi-Matrixformel Summenprodukt so:
=INDEX(B$1:B$999;SUMMENPRODUKT((A$1:A$999>="25.02.2016"*1)*(A$1:A$999<"25.02.2016"+1)*ZEILE(A$1:A$999)))
Ginge auch mit =indirekt("B"&Summenprodukt())
Naja das deutsche Pendant sverweis hat ja auch 4 und nicht 3 Werte.
Der Bereichsverweis ist standardmäßig aktiviert, damit findet Excel dann auch werte die so ungefähr passen und die liste muss auch aufsteigend sortiert sein. Daher eine komisch Wahl als Standardverhalten.
Ändere deine Funktion auf
=vlookup(DATEVALUE("25.02.2016"); $A$37:$B$999;2;0)
Hi Jackie, danke für die schnelle Antwort.
Es funktioniert nicht kann man eine andere Formel anwenden? Das datumsformat (tt.mm.yyyy hh:mm:ss) kann man nicht ändern!
Ich suche nur nach den Datumwerten. und aus spalte b soll der wert in Zelle c zurückgegeben werden.
achso, du suchst nach werten die quasi nicht vorhanden sind - wäre da nicht eine Hilfspalte sinnig die aus dem Datum + zeitwert zunächst das Datum filtert?
Also
=Integer(A37)
in Zelle B37 neu einführen und dann dein Datum in Spalte B suchen lassen, wo es das dann auch gibt?
Sorry die korrekte Formel muss natürlich
=Int(A37) lauten
ich hätte nochmal nachschauen sollen, arbeite nie mit der englischen version
ich hab mir für die gezielte Feststellung des englischen Namens diese kleine µ-Formel geschrieben:
- Function FormelEN(Ort)
- FormelEN = Ort.Formula
- End Function 'für Übersetzung DE=>EN
Die Funktion hab ich in meiner Forums-Begleitdatei i'wo in den fixierten Kopfzeilen hinterlegt, so dass ich sie ganz schnell anwenden kann. (mir war die Suche in Übersetzungslinks zu lästig, dauert auch länger als das hier).
Hab das jetzt doch noch schnell ausprobiert: mit SVerweis krieg ichs nicht hin aber mit index/Vergleich gehts (zumindest mit kleinen Bereichen und im deutschen xl, hier übersetzt::
{=INDEX(B$37:B$99,MATCH("25.02.2016"*1,INT($A$37:$A$99),0))}
Wie das mit dem Erkennen des deutschen Datums ist, konnte ich nicht prüfen, aber 1. in Gänsefüßchen, 2. *1 : das macht die Datumszahl draus. Und wieder: Matrixformel!