Lookup Functions: XLOOKUP, VLOOKUP and INDEX MATCH
Data is rarely in one table. Prices live in a product list, targets in a rep list. A lookup brings a value from one table into another by matching on a shared key.
Create a second table called Reps:
| Rep | Manager | Target |
|---|---|---|
| Asha | Meera | 3000 |
| Ben | Tom | 2500 |
| Chen | Meera | 2000 |
| Diana | Tom | 1500 |
XLOOKUP
XLOOKUP is available in Microsoft 365 and Excel 2021 or later.
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])Add a Manager column to the Sales table:
=XLOOKUP([@Rep],Reps[Rep],Reps[Manager],"Not found")It searches Reps[Rep] for the rep on the current row and returns the value from the same position in Reps[Manager]. By default it looks for an exact match. The fourth argument is what to return if nothing matches.
VLOOKUP
Older workbooks use VLOOKUP, so you need to be able to read it.
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])=VLOOKUP([@Rep],Reps,2,FALSE)Three things to know:
- It searches only the first column of the table and returns from a column to the right.
- The column is identified by number, so inserting a column can break the formula.
- If you leave out the last argument, it performs an approximate match. Type
FALSEfor an exact match.
INDEX and MATCH
This pair works in every version of Excel and can look in any direction.
=INDEX(Reps[Manager],MATCH([@Rep],Reps[Rep],0))MATCH finds the position of the rep in the list, and the 0 asks for an exact match. INDEX returns the item at that position from the Manager column.
Which to use
| XLOOKUP | VLOOKUP | INDEX and MATCH | |
|---|---|---|---|
| Exact match by default | Yes | No | You specify it |
| Can return a column to the left | Yes | No | Yes |
| Built-in “not found” value | Yes | No | No |
| Works in older versions | No | Yes | Yes |
When a lookup fails
#N/A means no match was found. The usual causes are extra spaces, a number stored as text on one side, or a key that really is missing. Clean the key columns first, as in the previous lessons.
Practice
Add a Target column to Sales with XLOOKUP, then a column showing each order’s revenue as a share of the rep’s target.