You have an order list with product codes and a separate price sheet. You need the prices next to the orders. So you copy and paste, one by one, or you try VLOOKUP, get a #N/A error, and go back to copying and pasting. Lookups are one of the most useful skills in Excel, and also one of the most frustrating at first. Once they click, though, you'll use them every week… for prices, customer details, product categories, and more.
Let's compare the three main options and when to use each.
What a lookup does
A lookup finds a value in one list and returns related information from another column. For example:
- Find a product code and return its price
- Find a customer ID and return their email
- Find an employee name and return their hourly rate
All three functions below do this. They just do it differently.
VLOOKUP: the classic
=VLOOKUP(A2, Prices!A:C, 3, FALSE)
In plain English: look for the value in A2, in the first column of Prices!A:C, and return the value from the third column. FALSE means exact match.
Strengths:
- Works in every version of Excel
- Widely known, so coworkers and old files probably use it
Weaknesses:
- The lookup value must be in the first column of the range. It can't look to the left.
- The column number is hard-coded. If someone inserts a column in the price sheet, the formula can quietly return the wrong data.
- If you forget FALSE at the end, it may return an approximate match, which can give wrong answers without any error.
XLOOKUP: the modern option
=XLOOKUP(A2, Prices!A:A, Prices!C:C, "Not found")
In plain English: look for A2 in column A of Prices, and return the matching value from column C. If it's not found, show "Not found."
Strengths:
- Exact match by default, so fewer silent mistakes
- Can look left or right, because you choose the lookup and return columns separately
- Inserting columns doesn't break it
- Built-in "if not found" message
- Can search from the bottom up, which helps when you want the most recent entry
Weaknesses:
- Only available in Microsoft 365, Excel 2021, Excel 2024, and Excel for the web. If you share files with people on older versions, they'll see errors.
INDEX and MATCH: the reliable workhorse
=INDEX(Prices!C:C, MATCH(A2, Prices!A:A, 0))
In plain English: find the position of A2 in column A, then return the value at that same position in column C.
Strengths:
- Works in every version of Excel
- Can look left or right
- Doesn't break when columns are inserted
Weaknesses:
- Two functions in one formula can feel confusing at first
Which should you use?
A simple rule:
- Everyone who will use the file has Microsoft 365 or Excel 2021 or later? Use XLOOKUP.
- Some people are on older versions? Use INDEX and MATCH.
- Maintaining an old file full of VLOOKUPs? It's fine to leave them, but double-check they all use FALSE for exact match.
Example: adding prices and categories to an order list
A small coffee roaster has an order sheet with SKU in column B, and a product table named tblProducts with SKU, Product Name, Category, and Price.
To add the price to each order:
=XLOOKUP(B2, tblProducts[SKU], tblProducts[Price], "Check SKU")
To add the category:
=XLOOKUP(B2, tblProducts[SKU], tblProducts[Category], "Check SKU")
Now a PivotTable can summarize sales by category, and anything that says "Check SKU" points to a typo or a missing product.
Why lookups fail (and how to fix them)
Extra spaces. "Mocha " with a trailing space doesn't match "Mocha." Use TRIM on both lists.
Numbers stored as text. A product code of 1001 stored as a number won't match "1001" stored as text. Make both columns the same type. One quick fix is using VALUE() or TEXT() in a helper column.
Approximate match by accident. In VLOOKUP, always use FALSE unless you specifically need an approximate match, like tax brackets or commission tiers.
Duplicates in the lookup list. Lookups return the first match. If a SKU appears twice with different prices, you might get the wrong one. Clean duplicates from reference tables.
Hiding errors too quickly. Wrapping everything in IFERROR to show blanks can hide real problems. Show a message like "Check SKU" instead, so you can find and fix the issue.
A tip: use Tables for your reference lists
Turn your price list or customer list into an Excel Table with Ctrl+T, then name it. Formulas like tblProducts[Price] are easier to read than Prices!$C$2:$C$500, and they grow automatically when you add new rows.
Practice exercise
Take any two lists you use, like a customer list and an invoice export. Add one XLOOKUP (or INDEX/MATCH) to bring a customer's city or email into the invoice list. Then use that new column to filter or summarize. That one formula can save you hours over a year.
Go beyond lookups
Lookups get much more powerful when you combine them with clean data, SUMIFS, and PivotTables. Our Advanced Excel for Business Analysis course covers XLOOKUP, INDEX/MATCH, and VLOOKUP side by side, plus the data cleaning skills that make lookups work reliably.