find last match in column using built in functions in excel

Question

I have an excel workbook with multiple instances of the same client ID. So, a client can appear more than once in the list. I would like to find the last (closest to the bottom) instance of the client ID so i can then look along that same row for additional information. VLOOKUP shows the fist instance, I need the last.

So, how do I find the last instance of a given client ID using built in functions? I'd rather not write a macro to do this.

Answer 1

Suppose you want to find last instance of id "id_1" in range A2:A8 and return corresponding value from range B2:B8, then use:

=LOOKUP(2,1/(A2:A8="id_1"),B2:B8)

---

If you want to return index of last intance of id "id_1" in range A2:A8, use:

=MATCH(2,1/(A2:A8="id_1"))

with array entry (CTRL+SHIFT+ENTER).

If you want to return row number of last intance of id "id_1", use:

=LOOKUP(2,1/(A2:A8="id_1"),ROW(A2:A8))

This syntax is a special trick: (A2:A8="id_1") evaluates to {TRUE,FALSE,FALSE,TRUE,...}. Next, assuming that TRUE=1 and FALSE=0, the part 1/{TRUE,FALSE,FALSE,TRUE,...} gives you {1,#DIV0!,#DIV0!,1,..}.

To return last number in resulting array lookup_value should be greater than any value in lookup_array ({1,#DIV0!,#DIV0!,1,..} in our case). Since array contains only 1 or #DIV0!, we can take 2 as lookup_value, because it's always greater than 1.