Beispielsweise wird meine Tabelle, die ich „Tabelle1“ nennen werde (ich habe dies als Namen definiert), ergänzt, wenn neue Daten eingehen. Wie kann ich mithilfe von VLookup den Wert der 2. Spalte des endgültigen (letzten) Datenpunkts am Ende von „Tabelle1“ abrufen?
Antwort1
Ist die erste Spalte numerisch oder Text? Sie können die Bereichssuchoption verwenden und nach einem Wert suchen, der größer ist als jeder Wert in Ihrer Tabelle. Wenn Ihre erste Spalte Wörter enthält, versuchen Sie:
=VLOOKUP("ZZZZZ", Table1, 2, TRUE)
oder, wenn Ihre erste Spalte eine fünfstellige Zahl ist, versuchen Sie:
=VLOOKUP("99999", Table1, 2, TRUE)
Und mir ist gerade eingefallen, dass das auch funktioniert, aber langsamer ist. Definieren Sie die erste Spalte von Table1 als Table1Col1.
=VLOOKUP(MAX(Table1Col1), Table1, 2, TRUE)
Antwort2
Wenn Sie die ausgewählte Spalte dynamisch aktualisieren möchten, können Sie den Wert col_index_num mithilfe einer MATCH()-Formel ausfüllen. Beispiel:
=VLOOKUP("d", Table1, 2, FALSE)
gibt den Wert in der zweiten Spalte zurück. Dabei gilt:
=VLOOKUP("d",Table1[#All],MATCH("value2",Table1[#Headers],0),FALSE)
gibt den Wert in der Spalte mit der Überschrift value2 zurück. Noch dynamischer können Sie es wie folgt schreiben:
=VLOOKUP("d", Table1, MATCH(Table1[[#Headers],[value2]],Table1[#Headers],0), FALSE)
die automatisch aktualisiert wird, wenn Sie die Spaltenüberschriften der Tabelle umbenennen
Antwort3
=VLOOKUP(A2,Table1,2,MAX(Table1Col2))
Erläuterung: =VLOOKUP(**Data being looked up**,**Name of Table or data range**,**column of data being looked up**,MAX(**Name or data range of data being looked up**))
Es wird der letzte Wert der Zeilen zurückgegeben, die dem Lookup_Value entsprechen.
Antwort4
Die Beantwortung erfolgt hauptsächlich, um aktuellen Suchern eine moderne Antwort zu geben („modern“ = 2021).
Sie würden verwenden XLOOKUP(). Es ist kein perfekter Ersatz und wird für manche Situationen nicht geeignet sein. Aber dieser ist einfach und seine Betaversion wurde für diesen Zweck neu entwickelt.
Das Interessante daran ist, dass es einen Parameter hat, mit dem man vom Ende zurück zum Anfang suchen kann, also ... Slamdunk.
Beim Lesen der Antworten ist mir jedoch aufgefallen, dass es keine definitive Antwort auf die Frage in Begriffen gibt, die beim Posten funktioniert hätten. Die mittlere Antwort geht überhaupt nicht auf die Frage ein und die dritte ist bis zum Äußersten verlogen. Sie verwendet etwas Ausgefallenes, um den Booleschen Wert zu generieren, TRUEobwohl einfaches Eintippen TRUE(oder 0wer auch immer das bevorzugt) genau dasselbe bewirken würde. Es erweckt fast den Eindruck, der Antwortende habe gehofft, dass etwas Ausgefallenes, etwas Anmaßendes den Anschein eines Werts erwecken würde. Igitt. Und es ist NICHT NICHT NICHT richtig, denn obwohl es eine nicht falsche Antwort geben KANN, ist dies reiner Zufall. Eine ordentliche Chance, da die Hauptfehlerursache darin liegt, dass der gewünschte Wert das allerletzte Element im Bereich ist, aber da dies insbesondere datumsbasiert sein könnte, besteht eine ziemlich gute Chance, dass dies passiert.
Die erste Antwort gibt einige praktische Dinge zum Ausprobieren an und je nach Ihren Daten könnte eines davon oder etwas, das davon inspiriert ist, für Sie gut funktionieren. Nicht wie die dritte, die ziemlich schwindelerregend ist und vielen ein starkes Gefühl des Vertrauens in die Ergebnisse vermitteln würde, sodass sie sprachlos wären, wenn der Chef kommt und über einen Misserfolg schreit.
Es handelt sich jedoch nur um praktische Dinge und nicht unbedingt um Lösungen. Es gibt einen Standardansatz dafür, der „überall“ im Internet verfügbar ist und LOOKUP()auf clevere Weise verwendet wird. Aber wie wäre es mit etwas viel Direkterem und leichter verständlicherer Funktionsweise? (Für die meisten ist die LOOKUP()Methode, obwohl clever, wirklich clever und eigentlich wirklich einfach, einfach verwirrend und verwirrt sie auf den ersten Blick. Sie könnten sie also LEICHT verstehen, kommen aber nie an den Punkt, an dem sie es realisieren.)
Mit der INDEX()Funktion kann man eine Tabelle sozusagen auf den Kopf stellen, so dass die erste Zeile, die dem Rest der Formel präsentiert wird, die letzte Zeile im ursprünglichen („physischen“) Bereich ist. Dies geschieht, indem man für den Zeilenparameter etwas angibt, das eine Zahlenfolge generiert, die mit der höchsten Zeilennummer (höchster „Index“) beginnt und bis 1 reicht.
Dies wurde 2012/2013 mithilfe eines cleveren Tricks erreicht, um die Zeilennummern in einer Sequenz zu generieren: ROW(1:100)ergibt eine Sequenz von 1, 2, 3, ... 98, 99, 100. Um es umzukehren, könnte man meinen, man könnte einfach verwenden, ROW(100:1)aber Excel „korrigiert“ Ihren dummen Fehler hilfreicherweise ROW(1:100)– ob Sie es wollen oder nicht. Das lässt sich nicht umgehen. Aber wenn Sie die höchste verwendete Zeilennummer kennen und dies kein Problem wäre, können Sie 1 dazu addieren, also in diesem Beispiel 100 + 1, und dann die Sequenz, die Excel Ihnen gibt, davon abziehen. Da Sie 101 – 1, 100 – 2, 100 – 3, … haben, erhalten Sie 100, 99, 98, … bis hinunter zu 101 – 100 oder 1.
Verwenden Sie dies für den Zeilenparameter, um INDEX()die Zeilen umzukehren und von der letzten zur ersten zu gelangen.
Für die Spalten für INDEX()müssen Sie über die üblichen Erfahrungen mit hinausdenken VLOOKUP(). Normalerweise geben Sie eine Tabelle/einen Bereich an, der viele Spalten haben kann. Also vielleicht A2:W900. Sie möchten Spalte A durchsuchen und etwas in Spalte T (oder Spalte 20) finden. Sie könnten also etwas wie haben VLOOKUP("cow",A2:W900,20,false). Nun, INDEX()ermöglicht Ihnen die Verwendung eines kleinen Tricks, um nur bestimmte Spalten anzugeben, anstatt alle Spalten im angegebenen Bereich zu verwenden: die Array-Konstante.
Array Constantssind eine Möglichkeit, Excel ein Array von Werten zu übergeben. Sie sehen folgendermaßen aus: {"a","cow",13,999,"fisherman","circu"}. So in this example, you want to have columns A and T, or 1 and 20, so you'd giveINDEX() {1,20}` für den dritten Parameter, den Spaltenparameter.
Der Sinn all dessen besteht darin, dass INDEX()eine virtuelle Tabelle erstellt wird, die Sie in der Formel anstelle der realen Tabelle verwenden können. Die virtuelle Tabelle hat nicht die Zeilen 2-900 und die Spalten AW. Sie hat nur die Zeilen 900 bis 2 und die Spalten A und T. Diese Funktion würde für das Beispiel folgendermaßen aussehen:
INDEX( A2:W900, 900-ROW(1:899), {1,20} )
(Denken Sie daran, dass Sie die Zeilen 2 bis 900 verwenden, sodass Sie nur 899 Zeilen haben, nicht 900. Die Excel-Zeile 2 ist also der „Index“ 1 im Bereich und die Excel-Zeile 900 ist der Index 899. 899 + 1 = 900, sodass Sie 900 verwenden, nicht 901. Dann ergeben die Zahlen in der Formel „Sinn“. So als würden Sie die erste Festplatte „Laufwerk 0“ und nicht „Laufwerk 1“ nennen.)
Jetzt haben Sie eine virtuelle Tabelle, die NUR aus den Spalten A und T besteht, Zeile 900 zuerst, dann Zeile 2 zuletzt, und das ist Ihre Tabelle, die Sie in verwenden VLOOKUP(). Die vollständige Formel zum Nachschlagen des Wortes „Kuh“ und zum Finden einer exakten Übereinstimmung ... beginnend am ENDE und zurück zum Anfang - würde also folgendermaßen aussehen:
=VLOOUP( "cow", INDEX( A2:W900, 900-ROW(1:899), {1,20} ), 2, false )
Da die Daten der „echten“ Tabelle die Zeilen 2-900 umfassen, wird hier die LETZTE Übereinstimmung mithilfe einer Tabelle ermittelt, die die Daten in die Zeilen 900-2 umgedreht hat. Dabei werden nur die beiden Spalten verwendet, die, in der Sie nachschlagen, und die, aus der Sie die Daten erhalten, nicht alle 23 Datenspalten. Da es nur zwei Spalten gibt, ist die Spalte, aus der eine Antwort zurückgegeben wird, immer EINFACH: Es ist Spalte 2. „false“ stellt eine genaue Übereinstimmung sicher, falls vorhanden.
Und das wird IMMER funktionieren.
Abgesehen davon, die Array-Konstante, die wir verwendet haben, {1,20}... was würde Ihrer Meinung nach das Umkehren dieser Werte bewirken? Wie in {20,1}. Sie kehren die Reihenfolge der Spalten um. Wenn Sie also verwenden möchten VLOOKUP(), Ihre Nachschlagespalte in diesem Beispiel jedoch Spalte T ist und Sie Ergebnisse aus Spalte A möchten, haben Sie Pech gehabt. (Der alte „Unfall aus Pech“ aus meiner Zeit im Atomkraftprogramm der US-Marine. Mann, „OOL“-Unfall ... wir waren SO nah dran, „LOL“ Jahre früher zu erschaffen, aber ... haben es einfach nicht getan ... Nun, Reaktorunfälle waren wohl keine lustige Angelegenheit, also vielleicht das Beste, oder?) Ihre Nachschlagespalte MUSS links von Ihrer Ergebnisspalte stehen, um VLOOKUP()... no bueno! zu verwenden.
Da Sie jedoch eine virtuelle Tabelle erstellen, die die Spalten umkehrt, befindet sich Ihre Nachschlagespalte plötzlich doch LINKS! BUENO!
Eine weitere Anmerkung: Die Array-Konstante für die Spalten (oder Zeilen, aber normalerweise Spalten) kann nur einige Zahlen verwenden, diese in beliebiger Reihenfolge verwenden UND sie mehr als einmal verwenden, wenn Sie möchten.
Zur Erinnerung: „Moderne Methode“: Heutzutage können wir SEQUENCE()die umgekehrte Sequenz von 899 bis 1 direkt erzeugen.