Is it possible to use Index and Match or something similar in Microsoft Excel to find last filled number row for two sets of numbers in same column?

Question

Sample table image:

Objective: In the sample table image, I want to extract from column C, last populated CST number as 1235 and last populated Vat number as 204.

If i use =VLOOKUP("CST",B2:C5,2,FALSE) it returns the first CST value i.e. 1234. Like wise for VAT using VLOOKUP returns 203.

If I try Match, works fine for CST but for VAT same formula with a smaller lookup_value say 250 (since VAT values are below 250) gives #N/A as result.

Screenshot (Using MATCH with different lookup_value on same column):

Answer 1

For something like this I prefer AGGRAGATE to 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))

I put the actual criteria in D1 and D2 so I could reference them directly and not hard code "CST" and "VAT" into the formula, making it draggable.

The two $A$2:INDEX($A:$A,MATCH("ZZZ",$A:$A)) Dynamically set the the reference range as this is an array type formula. It is set from A2 to the last cell in column A that has a text string.

The Aggregate will return the largest row number (last row) that matches the criteria to the INDEX.

Answer 2

You can do this with LOOKUP.

LOOKUP When lookup_value is larger than any number in the array, the function returns the last number in the array; the 1/(1/(...)) construct converts the 0's to errors, so the last "number" will be the value in the same position as the last CST or VAT depending on the formula.

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)))

The above formulas assume the Invoice No is always a number, if it might be a string, the formula will need some modification.

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

Use VLOOKUP for vertical lookups. The second argument is a matrix. If there is no limitation in rows, don't specify the row numbers, just the column letters. The third argument tells, which column to use. 2 means the second, i.e. C in your matrix.

By default, the function does not stop on first find; it moves on to find more occurrences, ultimately resulting in finding the bottommost entry (typically being the newest), exactly as desired.

Hence use

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

and

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

Note, that there's also HLOOKUP for horizontal lookups.

Answer 4

In the day, I turned the tables upside down, so to speak, then used VLOOKUP(), when possible, and INDEX/MATCH when not. The important part is turning the table upside down and the following shows how to do that. Use that as the table to look up what one wants to look up and when it finds the first occurrence, it is finding the "real" last occurrence, as it sits in the original ("real") table.

So, table of values is A1:B22. Then:

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

Basically, it finds the number of rows (22), adds 1 (23), then makes an array of values which are that value (23) minus a series of values from 1 to the number of rows (22) giving one an array of rows to return: 23-1=22, 23-2=21, 23-3=20, etc. so 22, 21, 20, and so on down to 1. So INDEX() returns the rows in reverse order.

Nowadays (2022), we'd just use XLOOKUP() and search from the end to the beginning. Certainly, if doing the above, one would use SEQUENCE() to generate the number sequences.