INDEX and MATCH function are close cousins to the VLOOKUP function and their combination creates a more flexible and better lookup function.
The problem with a VLOOKUP Function
To use a VLOOKUP function, a data table has to be structured with the target columns after your lookup column. Any columns to the left of the lookup column can’t be retrieved unless you change the column order of data table.
In the below table, if email address is your lookup column, you can only pull City with a VLOOKUP.
Using a combination of the INDEX and MATCH function as a more advanced VLOOKUP Function
The INDEX Function takes in three arguments and here is how you can turn it into a sweet dynamic lookup function:
- A data array –
- Set this to your target column
- A row number
- Use MATCH to make this dynamic. This function returns the row number based on the cell you are looking up in a data array
- The third value in the MATCH function should be 0 for an exact match. If you don’t use 0 or leave out the value, you’ll get weird results
- A column number
- Set this to 1 since your data array only has one column
Benefits of using this method
Figuring out the column number in data tables with a large number of columns can be a headache. With INDEX and MATCH, you no longer have to figure out the column number.
Keeping the Index column unlocked and the match column locked, allows you to copy the formula to the right and dynamically select the subsequent column.
Try the new XLOOKUP function in Excel
The new XLOOKUP function in certain versions of excel solves the problems of VLOOKUP and operates in the same way as my INDEX and MATCH functions. It takes three arguments:
- The cell you want to lookup
- Lookup column
- Return column