Excel Lookup Guide: VLOOKUP, XLOOKUP and INDEX-MATCH
Master Excel lookups: VLOOKUP, XLOOKUP and INDEX-MATCH explained with syntax, worked examples, common errors, and which to use when.
The Lookup Problem
Every business spreadsheet eventually needs the same thing: find a value in one table and bring back related information from another. Employee ID to department. Product code to price. Transaction reference to description. Invoice number to customer name.
Excel gives you three ways to do it: VLOOKUP, XLOOKUP, and INDEX-MATCH. This guide covers all three with syntax, worked examples, the errors that catch people out, and a clear rule for which one to use.
If you want the manager's digest version of the same functions alongside other time-savers, see our 10 time-saving Excel formulas guide.
VLOOKUP: The Classic
VLOOKUP searches for a value in the first column of a range, then returns a value from any other column in the same row.
Syntax:
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
lookup_value: what you are searching fortable_array: the range to searchcol_index_num: which column of the range to return (1 is the first column)range_lookup: FALSE for exact match, TRUE for closest match. Always use FALSE unless you specifically need the closest-match behaviour
Worked example: you have an employee table with ID in column A, name in B, department in C. To find the department for employee E103:
=VLOOKUP("E103", A2:C500, 3, FALSE)
The formula searches column A for "E103", finds it on row 103, and returns the value from column 3 of the range (C103).
Real business use: a pricing table lookup. Product code in column A, unit price in B, cost in C. A sales report formula pulls the price:
=VLOOKUP(A2, Products!A:C, 2, FALSE)
VLOOKUP limitations
VLOOKUP has four structural weaknesses:
- Left-column only. The lookup column must be the leftmost column of the range. You cannot search column B and return from column A.
- Column insertions break it. Add a column inside the table range and the
col_index_numsilently points at the wrong data. No error, just wrong numbers. - One value at a time. It returns a single value. Pulling five fields means five formulas.
- Column index is a magic number. Nothing in the formula tells you what column 3 means. Six months later, nobody knows.
XLOOKUP: The Modern Replacement
XLOOKUP searches a range in any direction and returns a matching value. It is available in Excel 2021 and Microsoft 365.
Syntax:
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
lookup_value: what you are searching forlookup_array: the range to search (any column)return_array: the range to return from (any column, any position)if_not_found: optional text to show instead of #N/Amatch_mode: 0 exact (default), -1 exact or next smaller, 1 exact or next larger, 2 wildcard
Same employee example:
=XLOOKUP("E103", A2:A500, C2:C500)
No column index, no FALSE argument, and it returns #N/A with a built-in fallback:
=XLOOKUP("E103", A2:A500, C2:C500, "Employee not found")
Left lookup, which VLOOKUP cannot do:
=XLOOKUP("E103", B2:B500, A2:A500)
VLOOKUP to XLOOKUP migration table
| Task | VLOOKUP | XLOOKUP |
|---|---|---|
| Exact match, right column | =VLOOKUP(A2, B:C, 2, FALSE) | =XLOOKUP(A2, B:B, C:C) |
| Missing value fallback | =IFERROR(VLOOKUP(...), "Not found") | =XLOOKUP(..., "Not found") |
| Lookup not in first column | Not possible | =XLOOKUP(A2, C:C, B:B) |
| Return multiple columns | One formula each | =XLOOKUP(A2, B:B, C:D) returns two cells |
| Approximate match | TRUE in last argument | match_mode argument |
INDEX-MATCH: The Flexible Veteran
INDEX-MATCH pairs two functions: MATCH finds the position of a value in a range, and INDEX returns the value at a given position in another range.
Syntax:
=INDEX(return_range, MATCH(lookup_value, lookup_range, 0))
Same employee example:
=INDEX(C2:C500, MATCH("E103", A2:A500, 0))
MATCH locates the row where A equals "E103", and INDEX returns the value from column C on that row.
Two-way lookup: find a value at the intersection of a row and a column. Say you need the sales figure for region "East" in month "Mar":
=INDEX(B2:M13, MATCH("East", A2:A13, 0), MATCH("Mar", B1:M1, 0))
This is the classic lookup table pattern for dashboards and scenario summaries.
Why INDEX-MATCH still earns its place: it looks left, it survives column insertions (the lookup and return ranges are defined separately), and on very large datasets it is generally faster than VLOOKUP because MATCH scans a single column rather than VLOOKUP scanning the whole table array.
Common Lookup Errors and Fixes
#N/A: value not found
The lookup value is not in the lookup column, or the match mode is wrong.
| Symptom | Likely cause | Fix |
|---|---|---|
| #N/A on a value you can see | Text vs number mismatch | =VLOOKUP(TEXT(A2,"0"), ...) or fix the data type |
| #N/A on a value you can see | Leading/trailing spaces | =VLOOKUP(TRIM(A2), ...) and clean the table with TRIM |
| #N/A only on some rows | Approximate vs exact match | Use FALSE (VLOOKUP) or 0 (XLOOKUP match_mode) |
| #N/A after data import | Imported numbers stored as text | Select the column, use Text to Columns, finish |
#REF!: the range is broken
A column was deleted from the table range. Fix the range reference, then consider XLOOKUP with separate ranges so this cannot happen.
#VALUE! or wrong results with approximate match
Approximate match (TRUE / match_mode 1 or -1) assumes the lookup column is sorted. If it is not, results are meaningless. Use exact match unless you are doing bracket lookups like tax thresholds, which must be sorted ascending.
Lookup returning the wrong row
Check for duplicate values in the lookup column. Lookups return the first match. If duplicates exist, add a helper column that concatenates the unique combination, for example =A2&"-"&B2, and look that up instead.
Which One Should You Use?
| Situation | Use |
|---|---|
| New workbooks, Excel 2021 or 365 | XLOOKUP |
| Maintaining legacy workbooks | VLOOKUP (leave the originals working) |
| Look left or two-way lookups | INDEX-MATCH |
| Older Excel versions, no XLOOKUP | INDEX-MATCH or VLOOKUP |
| Very large datasets | INDEX-MATCH (or XLOOKUP with sorted data and search_mode 2) |
The practical rule: build new work with XLOOKUP, keep VLOOKUP working in old files, and reach for INDEX-MATCH when you need the flexibility. All three share the same core idea, so learning one makes the others straightforward.
Frequently asked questions
What is the difference between VLOOKUP and XLOOKUP?
XLOOKUP is the modern replacement. It can search in any column (not just the leftmost), return values from any column, handle missing values with a built-in not_found argument, and supports both exact and approximate matches with a separate match_mode argument. VLOOKUP still works and is the right choice for legacy workbooks, but XLOOKUP is simpler and more flexible in Excel 2021 and Microsoft 365.
When should I use INDEX-MATCH instead of VLOOKUP?
Use INDEX-MATCH when you need to look left (return a value from a column to the left of the lookup column), when you work in Excel versions without XLOOKUP, or when you need a two-way lookup that matches both a row and a column. On large datasets INDEX-MATCH also tends to calculate faster than VLOOKUP because MATCH only scans the lookup column.
Why is my VLOOKUP returning #N/A when the value clearly exists?
The usual causes are: the lookup value is text while the table contains numbers (or vice versa), leading or trailing spaces in either the lookup value or the table, or using approximate match (TRUE) when you need exact match (FALSE). Wrap lookup values in TRIM, check data types with ISNUMBER, and always use FALSE for exact lookups unless you specifically need the closest-match behaviour.
Is VLOOKUP still worth learning in 2026?
Yes, because millions of existing workbooks still use it and you will maintain them. Learn VLOOKUP for compatibility and XLOOKUP for new work. INDEX-MATCH remains valuable for two-way lookups and older environments. The lookup logic you learn with one transfers directly to the others.