Ist es möglich, in Microsoft Excel „Index and Match“ oder etwas Ähnliches zu verwenden, um die letzte ausgefüllte Zahlenzeile für zwei Zahlensätze in derselben Spalte zu finden?

Question

Beispieltabellenbild:

Ziel: Im Beispieltabellenbild möchte ich aus Spalte C die zuletzt eingetragene CST-Nummer 1235 und die zuletzt eingetragene Mehrwertsteuernummer 204 extrahieren.

Wenn ich =VLOOKUP("CST",B2:C5,2,FALSE) verwende, wird der erste CST-Wert, also 1234, zurückgegeben. Ebenso wird für die Mehrwertsteuer mit VLOOKUP der Wert 203 zurückgegeben.

Wenn ich „Match“ versuche, funktioniert es für CST einwandfrei, aber für die Mehrwertsteuer ergibt dieselbe Formel mit einem kleineren Lookup-Wert, sagen wir 250 (da die Mehrwertsteuerwerte unter 250 liegen), das Ergebnis #N/A.

Screenshot (Verwenden von MATCH mit unterschiedlichen Lookup-Werten in derselben Spalte):

Answer 1

Für so etwas bevorzuge ich AGGRAGATE gegenüber MATCH:

=INDEX($B:$B,AGGREGATE(14,6,ROW($A$2:INDEX($A:$A,MATCH("ZZZ",$A:$A)))/($A$2:INDEX($A:$A,MATCH("ZZZ",$A:$A))=D$1),1))

Ich habe die eigentlichen Kriterien in D1 und D2 eingefügt, damit ich direkt auf sie verweisen kann und sie nicht fest codieren "CST"und "VAT"in die Formel einfügen muss, sodass sie verschoben werden kann.

Die beiden $A$2:INDEX($A:$A,MATCH("ZZZ",$A:$A))legen den Referenzbereich dynamisch fest, da es sich um eine Formel vom Typ Array handelt. Er wird von A2 bis zur letzten Zelle in Spalte A festgelegt, die eine Textzeichenfolge enthält.

Das Aggregat gibt die größte Zeilennummer (letzte Zeile) zurück, die den Kriterien für den INDEX entspricht.

Answer 2

Dies können Sie mit tun LOOKUP.

LOOKUP Wenn lookup_valuegrößer als jede Zahl im Array ist, gibt die Funktion die letzte Zahl im Array zurück. Die 1/(1/(...))Konstruktion konvertiert die 's in Fehler, sodass die letzte „Zahl“ der Wert an derselben Position wie der letzte oder, abhängig von der Formel 0, der letzte ist .CSTVAT

Last CST:  =LOOKUP(9E+307,1/(1/(($B$2:$B$999="CST")*$C$2:$C$999)))
Last VAT:  =LOOKUP(9E+307,1/(1/(($B$2:$B$999="VAT")*$C$2:$C$999)))

Bei den obigen Formeln wird davon ausgegangen, dass die Rechnungsnummer immer eine Zahl ist. Wenn es sich um eine Zeichenfolge handeln könnte, muss die Formel etwas geändert werden.

Last CST:  =LOOKUP(2,1/(($B$2:$B$999="CST")*ROW($B$2:$B$999)),$C$2:$C$999)
Last VAT:  =LOOKUP(2,1/(($B$2:$B$999="VAT")*ROW($B$2:$B$999)),$C$2:$C$999)

Answer 3

Verwenden Sie SVERWEIS für vertikale Nachschlagevorgänge. Das zweite Argument ist eine Matrix. Wenn es keine Zeilenbeschränkung gibt, geben Sie nicht die Zeilennummern an, sondern nur die Spaltenbuchstaben. Das dritte Argument gibt an, welche Spalte verwendet werden soll. 2 bedeutet die zweite, also C in Ihrer Matrix.

Standardmäßig stoppt die Funktion nicht beim ersten Fund, sondern sucht weiter nach weiteren Vorkommen, bis schließlich – genau wie gewünscht – der unterste Eintrag (normalerweise der neueste) gefunden wird.

Daher verwenden

=VLOOKUP("CST", B:C, 2)

Und

=VLOOKUP("VAT", B:C, 2)

Beachten Sie, dass es auch HLOOKUP für horizontale Nachschlagevorgänge gibt.

Answer 4

An dem Tag habe ich die Tabellen sozusagen auf den Kopf gestellt und dann verwendet VLOOKUP(), wenn möglich und INDEX/MATCHwenn nicht. Der wichtige Teil ist, die Tabelle auf den Kopf zu stellen, und im Folgenden wird gezeigt, wie das geht. Verwenden Sie diese als Tabelle, um nachzuschlagen, was Sie nachschlagen möchten, und wenn das erste Vorkommen gefunden wird, wird das „echte“ letzte Vorkommen gefunden, so wie es in der ursprünglichen („echten“) Tabelle steht.

Die Wertetabelle lautet also A1:B22. Dann:

=INDEX(A1:B22,  ROWS(A1:A22)+1-ROW(INDIRECT("1:"&ROWS(A1:A22))),  {1,2})

Grundsätzlich ermittelt es die Anzahl der Zeilen (22), addiert 1 (23) und erstellt dann ein Array von Werten, das diesem Wert (23) minus einer Reihe von Werten von 1 bis zur Anzahl der Zeilen (22) entspricht, wodurch ein Array von Zeilen entsteht, das zurückgegeben werden kann: 23-1=22, 23-2=21, 23-3=20 usw., also 22, 21, 20 und so weiter bis hinunter zu 1. Gibt also INDEX()die Zeilen in umgekehrter Reihenfolge zurück.

Heutzutage (2022) würden wir einfach verwenden XLOOKUP()und vom Ende zum Anfang suchen. Wenn man das oben genannte macht, würde man sicherlich verwenden, SEQUENCE()um die Zahlenfolgen zu generieren.