Hi - Dave here.
How do you do a left lookup with INDEX and MATCH?
This is actually a trick question. Unlike VLOOKUP, INDEX and MATCH doesn't care about lookup direction at all.
You can see how this works in the worksheet below. The formula in H5 is:
=INDEX(data[Item],MATCH(G5,data[ID],0))
The MATCH function finds the value in G5 (1003) in the ID column, and provides the position to INDEX, which returns a value at that row. The fact that the values being returned come from the
left of the ID column makes no difference.
[
download the workbook and read the full explanation]
The article provides a detailed explanation and includes the Excel workbook used in the example. I also show how to use XLOOKUP to get the same result. Download the workbook and try it out yourself.
Excel formulas
We maintain a large list of working formulas
here.
If you need more structure, we also offer
video training.
Have a fun and safe weekend!
Dave
