Excel Lookup Functions

Question

You have a data table and need to find data points in relation to other data in the table. The data you are looking for are to the left of (or above) the column (row) with the reference data, so you can't use VLOOKUP (HLOOKUP) to find your target data. Without rearranging or adding columns (rows) to your table, how can you still find your target data?

Answer 1

You can substitute the VLOOKUP and HLOOKUP functions in two steps: First, use the MATCH function to find the relative position of the row (column) within the column (row) with your reference data. Then use the INDEX function to find the associated target value in the corresponding row (column) of the column (row) with your target data.

Answer 2

Use the newly released "XLOOKUP" function. It eliminates the restrictions of the VLOOKUP and HLOOKUP, and does away with the need for an INDEX MATCH MATCH.