Lesson 3 of 8 · 7 min · Formulas
XLOOKUP, VLOOKUP and INDEX/MATCH
What you will learn
- Look up values with
XLOOKUP - Handle "not found" and return several columns
- Understand
VLOOKUP, its limits and theFALSEtrap - Use
INDEXandMATCHas a flexible alternative - Use approximate match for grade bands and tax slabs
In simple words
A lookup is like using a phone book: you know the name, and you want the phone number next to it. In Excel you have one table with employee ids and another with ids and names. A lookup takes an id from the first table, finds it in the second, and brings back the name. It saves you from copying and pasting by hand, and it stays correct when data changes.
Words to know
- Lookup value: the thing you are searching for, such as an id.
- Lookup array: the column where Excel searches.
- Return array: the column that holds the answer.
- Exact match: finding a value that is identical to the lookup value.
The tables
Orders (in a Table named Orders) have ProductID but no product name. A second Table named Products has ProductID, Name and Price.
XLOOKUP
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
In the Orders table, add a column for the name:
=XLOOKUP([@ProductID], Products[ProductID], Products[Name], "Not found")
Excel finds the ProductID in the Products column and returns the name from the same row. Why it is better than VLOOKUP:
- It looks left or right; the lookup column does not have to be first.
- Exact match is the default (no
FALSEto forget). - It has a built-in "not found" argument instead of needing
IFERROR. - It returns several columns at once:
=XLOOKUP(A2, Products[ProductID], Products[[Name]:[Price]])spills name and price into two cells. - It can search from the bottom (
search_modeof -1) to find the last match.
XLOOKUP needs Microsoft 365, Excel 2021 or later. In older files, use VLOOKUP or INDEX/MATCH.
Approximate match for bands
Match modes: 0 exact (default), -1 exact or next smaller, 1 exact or next larger, 2 wildcard. For a grade table sorted by lower bound:
| Score from | Grade |
|---|---|
| 0 | F |
| 40 | D |
| 60 | C |
| 75 | B |
| 90 | A |
=XLOOKUP(B2, Grades[From], Grades[Grade], , -1)
A score of 82 returns B (the next smaller bound is 75). The two commas leave if_not_found empty. This beats a long nested IF because you change the table, not the formula. The same pattern works for tax slabs, commission rates and shipping bands.
VLOOKUP
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
=VLOOKUP(A2, Products!A:C, 2, FALSE)
- The lookup value must be in the first column of
table_array. col_index_numcounts columns from the left of that range (2 means the second column).- The last argument matters:
FALSEmeans exact match. If you leave it out, it defaults toTRUE(approximate), which on unsorted data returns wrong answers without any error. Always typeFALSEfor exact lookups. - Inserting a column in the table can silently change which column the number 2 points to.
- It cannot look to the left.
You will still meet VLOOKUP in old workbooks and interviews, so know it, but prefer XLOOKUP for new work.
INDEX and MATCH
Two functions that work together and run in every Excel version:
MATCH(value, range, 0)returns the position of the value in a range (0 means exact).INDEX(range, position)returns the item at that position.
=INDEX(Products[Name], MATCH([@ProductID], Products[ProductID], 0))
It looks left or right and is not broken by inserted columns. Two-way lookup (row and column): =INDEX(B2:E10, MATCH(G1, A2:A10, 0), MATCH(G2, B1:E1, 0)) returns the value where a chosen row and column meet.
Lookup problems and fixes
| Symptom | Likely cause | Fix |
|---|---|---|
#N/A although the value is visible |
number stored as text in one column and number in the other, or trailing spaces | convert types; use TRIM; check with =ISNUMBER(cell) |
Wrong values from VLOOKUP |
approximate match used | add FALSE |
#REF! |
col_index_num larger than the table width |
count columns or use XLOOKUP |
| Duplicates in the lookup column | lookup returns only the first match | clean keys, or use a pivot or FILTER |
| Slow workbook | thousands of lookups on whole columns in old Excel | use Tables or exact-sized ranges |
Lookups from another file or sheet
Reference another sheet with Products!A:C or a Table name. Linking to a closed workbook creates an external link that breaks if the file moves; consider importing the data with Power Query (Get Data) instead.
Common mistakes
- Forgetting
FALSEinVLOOKUP. - Mismatched data types or hidden spaces causing
#N/A. - A
col_index_numthat breaks when columns are inserted. - Looking up non-unique keys and trusting the first match.
- Typing the lookup value instead of referencing a cell.
Try it yourself
- Create
ProductsandOrderstables and add a product name to each order withXLOOKUP. - Add price and compute the line total.
- Make a grade table and use approximate match to grade marks.
- Rewrite your lookup with
INDEX/MATCH. - Break a lookup on purpose with a trailing space, then fix it with
TRIM.
Check yourself
What advantages does XLOOKUP have over VLOOKUP?
It can look left, defaults to exact match, has a "not found" argument, returns several columns, and does not break when columns are inserted.
What does the last argument of VLOOKUP do?
FALSE requests an exact match; TRUE (the default) requests an approximate match on sorted data.
What does MATCH(x, range, 0) return?
The position of x within the range, for an exact match.
Why does a lookup return #N/A even though the value looks present?
Usually a type mismatch (text versus number) or hidden spaces.
Interview questions
How would you do a lookup that returns a value to the left of the key?
XLOOKUP, or INDEX/MATCH; VLOOKUP cannot look left.
Explain approximate match with an example.
It finds the largest lookup value that is less than or equal to your value in a sorted table, such as a score of 82 in bands 0, 40, 60, 75, 90, which returns the 75 band.
How do you look up on two criteria?
Join the criteria with & in both the lookup value and a helper column, or use XLOOKUP with a boolean array, =XLOOKUP(1, (A:A=x)*(B:B=y), C:C), or FILTER.
Summary
XLOOKUP(value, lookup_column, return_column, not_found)is the modern, safe lookup.- Approximate match with a band table replaces long nested
IFs. VLOOKUPneedsFALSEand the key in the first column;INDEX/MATCHworks everywhere.- Most
#N/Aproblems are mismatched types or stray spaces.
Going deeper
- XLOOKUP function: the official reference.
- VLOOKUP function: syntax and common problems.
- INDEX function and MATCH function: the pair.
- How to correct a #N/A error: troubleshooting.
See how much of this you can explain
A free mock interview asks you new questions on Excel for analysts for Data Analyst and scores every answer, with what to fix.