Looking up a value from another table is probably the most common Excel task in analytics. There are three main ways to do it, and knowing their differences prevents silent mistakes.
The examples use an Orders sheet with a product code in column A, and a Products table with Code, Name, Category and Price.
VLOOKUP
=VLOOKUP(A2, Products!A:D, 4, FALSE)Finds A2 in the first column of the range and returns the 4th column (Price).
Limits:
- It can only look to the right of the lookup column.
- The column number (4) is hard-coded; inserting a column in the range breaks the formula silently.
- The last argument defaults to approximate match. Always write FALSE for exact matches.
INDEX + MATCH
=INDEX(Products!D:D, MATCH(A2, Products!A:A, 0))MATCH finds the row position of A2 in the code column; INDEX returns the value at that position in the price column.
Advantages:
- Works in any direction (the return column can be left of the lookup column).
- Not affected by inserted columns.
- Works in every Excel version.
The 0 means exact match.
XLOOKUP
=XLOOKUP(A2, Products[Code], Products[Price], "Not found")Available in newer versions of Excel (Microsoft 365 and recent releases).
Advantages:
- Any direction, no column numbers.
- Exact match by default.
- A built-in value when nothing is found.
- Can search from the bottom (latest match) and return several columns at once.
Comparison
| VLOOKUP | INDEX-MATCH | XLOOKUP | |
|---|---|---|---|
| Look left | No | Yes | Yes |
| Breaks if columns inserted | Yes | No | No |
| Default match | Approximate | You choose | Exact |
| "Not found" value | Needs IFERROR | Needs IFERROR | Built in |
| Older Excel versions | Yes | Yes | No |
Which should you use?
- XLOOKUP if everyone using the file has a recent Excel.
- INDEX-MATCH if the file must work in older versions.
- VLOOKUP mainly to understand others' files, and always with FALSE.
Two-way lookups
To find a value by row and column (for example, sales for a product in a month):
=INDEX(SalesGrid, MATCH(A2, ProductList, 0), MATCH(B1, MonthList, 0))Common lookup problems
- #N/A even though the value exists: extra spaces or numbers stored as text. Use TRIM, or convert types consistently.
- Wrong value returned: approximate match by mistake.
- Duplicates in the lookup table: lookups return the first match only, which can hide data problems. Check duplicates first.
The same idea in SQL and pandas
A lookup is a LEFT JOIN: keep every order, bring in product details where found. In SQL that is a join; in pandas, a merge. Learning the connection makes the move from Excel to code much easier.
Practise what you just read
Reading helps; writing the code yourself is what sticks. Start here: