Looking Up Data in Excel: The Complete Guide
Almost every real spreadsheet job comes down to the same move: you have a value in one place and you need to pull related data from somewhere else. Excel gives you at least six different functions to do it, and each one fails in a different way when you pick wrong. This guide maps the entire lookup landscape—which function to reach for, the exact syntax, the traps that produce wrong answers silently, and the patterns (multi-criteria, cross-workbook, error handling) that the function reference pages never connect.
The Lookup Decision in 30 Seconds
Before any syntax, here is the decision that matters:
| Your situation | Use this | Why |
|---|---|---|
| Excel 365 / 2021 or later | XLOOKUP | Searches any direction, exact match by default, built-in if-not-found |
| Older Excel, return column is right of the key | VLOOKUP | Simple, everyone knows it |
| Older Excel, return column is left of the key | INDEX + MATCH | VLOOKUP physically cannot look left |
| Data arranged in rows, not columns | HLOOKUP or XLOOKUP | Horizontal orientation |
| You only need the position, not a value | MATCH (or XMATCH) | Feeds other formulas |
| Tiered rates, tax brackets, grading scales | Approximate-match lookup | Finds the right band, not an exact value |
| Two or more conditions must match | INDEX/MATCH array, XLOOKUP with &, or FILTER | Single-key lookups can’t do it natively |
| You need all matches, not just the first | FILTER | Every classic lookup stops at the first hit |
If you want a deeper head-to-head on the big three, we benchmarked them in XLOOKUP vs VLOOKUP vs INDEX MATCH. The rest of this guide covers the full toolbox and the situations that comparison doesn’t.
VLOOKUP: The Workhorse and Its Four Hard Limits
VLOOKUP searches the first column of a range and returns a value from a column you specify by number:
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])A typical exact-match lookup of an employee’s department from a table in A2:D500:
=VLOOKUP("E-1042", A2:D500, 3, FALSE)The mechanics, argument by argument, are covered in our VLOOKUP function reference, and if you’re brand new to it, start with the walkthrough in how to use VLOOKUP. What those pages can’t tell you is when to stop using it. VLOOKUP has four structural limits:
- It can’t look left. The lookup column must be the leftmost column of
table_array. If your ID is in column D and the name you want is in column B, VLOOKUP is out. - The column number is fragile. That
3in the formula means “third column of the range”—insert a column inside the range and your formula now returns data from the wrong field, with no error to warn you. The column index number is the single most common source of silent VLOOKUP corruption. - It defaults to approximate match. Omit the fourth argument and Excel assumes
TRUE. On unsorted data this returns garbage that looks plausible. TypeFALSE(or0) every time unless you specifically want banded lookups—see the approximate-match section below. - It returns only the first match. Duplicate keys? You’ll never see the second row. If duplicates are unexpected, find them before you trust any lookup results.
One mitigation worth adopting immediately: convert your data to an Excel Table or use a named range so the table reference expands automatically as rows are added. A VLOOKUP pointed at A2:D500 quietly ignores row 501.
HLOOKUP: Same Engine, Rotated 90 Degrees
HLOOKUP is VLOOKUP for data laid out horizontally—keys in the first row, results in rows below:
=HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])It shares every VLOOKUP limitation (can’t look “up,” fragile row index, approximate-match default). You’ll meet it in dashboards where months run across columns. Syntax details are in the HLOOKUP function reference, with a worked example in how to use HLOOKUP. In modern Excel, XLOOKUP handles horizontal data with the same syntax as vertical, which is one more reason it has displaced both older functions.
MATCH: The Position Finder
MATCH returns where a value sits in a single row or column, not the value itself:
=MATCH(lookup_value, lookup_array, [match_type])=MATCH("Q3", A1:F1, 0) returns 4 if Q3 is the fourth header. Use match_type 0 for exact, 1 for “largest value ≤ lookup” (data sorted ascending), -1 for “smallest value ≥ lookup” (sorted descending).
On its own, MATCH answers questions like “which row is this order on?” or “which column holds this month?”—see how to use the MATCH function for standalone uses. But its real job is feeding a position into INDEX, which brings us to the most important pattern in pre-365 Excel. Modern Excel also offers XMATCH, which defaults to exact match, supports search-from-last, and adds binary search modes.
INDEX + MATCH: The Power User’s Default
INDEX returns the value at a given row and column of a range:
=INDEX(return_range, row_num, [column_num])Hard-coding row_num is rarely useful. The pattern is to let MATCH compute it:
=INDEX(B2:B500, MATCH("E-1042", D2:D500, 0))Read it inside-out: MATCH finds which row of D2:D500 holds the ID; INDEX returns the value from that same row of B2:B500. Notice the lookup column (D) is right of the return column (B)—the left-lookup that VLOOKUP can’t do. Other advantages:
- Insert-proof. You point at the actual return column, not a count of columns, so inserting columns can’t silently break it.
- Faster on big data. Excel scans one column for the match and touches one cell for the return, instead of hauling the whole table into memory.
- Two-way lookups. Use a second MATCH for the column:
=INDEX(B2:M50, MATCH("Widgets", A2:A50, 0), MATCH("Mar", B1:M1, 0))finds the intersection of a row and a column—the classic matrix lookup that no single VLOOKUP can do.
Our step-by-step INDEX MATCH tutorial builds the pattern from scratch if the inside-out logic hasn’t clicked yet.
XLOOKUP: The Modern Answer
If you’re on Excel 365 or Excel 2021+, XLOOKUP should be your default:
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])The same employee lookup, with graceful failure built in:
=XLOOKUP("E-1042", D2:D500, B2:B500, "Not found")What it fixes, in one function:
| Old problem | XLOOKUP behavior |
|---|---|
| Approximate match by default | Exact match by default |
| Can’t look left/up | Lookup and return arrays are independent—any direction |
| Needs IFERROR wrapper | if_not_found argument is built in |
| First-match only, top-down | search_mode -1 searches from the bottom (last match) |
| No wildcards without setup | match_mode 2 enables * and ? wildcards |
| One return column | Return array can be multiple columns—results spill |
That last point deserves a note: pass B2:C500 as the return array and XLOOKUP spills both columns into adjacent cells. If something blocks the spill range you’ll hit a #SPILL! error—here’s how to fix it. For practical recipes, see how to use XLOOKUP.
The one real caveat: XLOOKUP formulas break for colleagues on Excel 2019 or earlier. If a workbook must travel, INDEX+MATCH remains the compatible choice.
LOOKUP: The Old Vector Function Worth Knowing
The plain LOOKUP function predates all of the above:
=LOOKUP(lookup_value, lookup_vector, result_vector)It only does approximate match and requires sorted data, which makes it wrong for most ID-style lookups. But it survives in two niches: it’s the tersest way to do banded lookups (=LOOKUP(score, {0,60,70,80,90}, {"F","D","C","B","A"}) with arrays typed inline), and the idiom =LOOKUP(2, 1/(A:A<>""), A:A) returns the last non-empty value in a column—a trick that works in every Excel version ever shipped. For the broader family, our lookup and reference functions overview catalogs the relatives: CHOOSE, OFFSET, and INDIRECT, which let you build lookups whose ranges are themselves computed.
Exact vs. Approximate Match: When “Wrong” Is Right
Every lookup function offers two matching philosophies, and confusing them is the #1 lookup bug.
Exact match (VLOOKUP FALSE, MATCH 0, XLOOKUP default): the lookup value must exist in the lookup column, or you get #N/A. Use it for IDs, SKUs, names, emails—anything where a near-miss is meaningless.
Approximate match (VLOOKUP TRUE, MATCH 1): Excel finds the largest value less than or equal to your lookup value. This is not sloppiness—it’s the correct tool for banded data: tax brackets, shipping tiers, commission scales, letter grades. Given this rate table:
| A (Threshold) | B (Rate) |
|---|---|
| 0 | 0% |
| 10,000 | 12% |
| 40,000 | 22% |
| 85,000 | 24% |
=VLOOKUP(52000, A2:B5, 2, TRUE) correctly returns 22%, because 40,000 is the largest threshold not exceeding 52,000. Two iron rules: the lookup column must be sorted ascending (so sort your data first), and a lookup value below the first threshold returns #N/A—which is why banded tables start at 0. XLOOKUP’s match_mode 1 flips the logic to “next larger item” and, unlike the classics, doesn’t require sorted data unless you also use binary search_mode.
Lookups Across Sheets and Workbooks
Nothing about lookup syntax changes across sheets—only the range reference does. The pattern for referencing another sheet is SheetName!Range, with single quotes when the name contains spaces:
=VLOOKUP(A2, 'Master Data'!A:D, 4, FALSE)
=XLOOKUP(A2, Products!B:B, Products!E:E, "")The easiest way to get the reference right: start typing the formula, then click over to the other sheet and drag-select the range—Excel writes the sheet prefix for you. We walk through the full workflow in VLOOKUP across two sheets and the general technique in pulling data from another sheet.
Across workbooks, the reference grows a bracketed file name: [Budget.xlsx]Sheet1!$A:$D. When the source workbook closes, Excel rewrites it as a full path. Three things to know before you build on this:
- Whole-column references to closed workbooks don’t refresh; use explicit ranges.
- Moved or renamed source files leave you with #REF! errors. Audit with find external links in Excel (Data → Edit Links).
- For anything recurring, importing via Power Query beats live external-workbook lookups—it’s refreshable, visible, and doesn’t break when a colleague reorganizes a shared drive.
Cross-sheet lookups are also the standard way to reconcile datasets—see comparing two sheets with VLOOKUP and matching data across two worksheets for those specific workflows.
Handling #N/A: IFNA, IFERROR, and Knowing the Difference
A lookup that can’t find its value returns #N/A. First, understand what #N/A actually means: “no answer exists,” which is information, not necessarily a malfunction.
To replace it with something presentable, you have three tools:
=IFNA(VLOOKUP(A2, Data!A:C, 3, FALSE), "Not found")
=IFERROR(VLOOKUP(A2, Data!A:C, 3, FALSE), "Not found")
=XLOOKUP(A2, Data!A:A, Data!C:C, "Not found")Prefer IFNA over IFERROR for lookups. IFERROR swallows every error—including the #REF! from a broken range or the #NAME? from a typo’d function. Wrap a broken formula in IFERROR and it reports “Not found” forever while being genuinely broken. IFNA catches only #N/A, so structural errors still surface. XLOOKUP’s if_not_found argument behaves like IFNA and is the cleanest option when available. For diagnostics rather than display, ISERROR and ISNA return TRUE/FALSE you can count or filter on.
When a lookup returns #N/A for a value you can see in the table, the value isn’t really there in the form you think. The usual culprits, in order of frequency:
- Trailing or invisible spaces—clean with TRIM, or wrap the lookup value:
VLOOKUP(TRIM(A2), …). - Numbers stored as text (or vice versa)—
"1042"will never match1042. Coerce withVALUE()or--. - Case isn’t the issue—lookups are case-insensitive. If you genuinely need case-sensitive matching, combine EXACT with INDEX/MATCH:
=INDEX(B:B, MATCH(TRUE, EXACT(A2, D:D), 0)).
Multi-Criteria Lookups: Three Patterns
Classic lookups take exactly one key. When you need “the price for this product in this region,” pick one of these:
1. Concatenated keys with XLOOKUP
=XLOOKUP(F2 & "|" & G2, A2:A500 & "|" & B2:B500, C2:C500, "No match")Joining criteria with a delimiter (the | prevents "ab"+"c" colliding with "a"+"bc") creates a virtual composite key on the fly. No helper column, no array-entry needed in modern Excel. This is usually the fastest pattern to write and read.
2. INDEX + MATCH with multiplied conditions
=INDEX(C2:C500, MATCH(1, (A2:A500=F2) * (B2:B500=G2), 0))Each comparison yields an array of TRUE/FALSE; multiplying them gives 1 only where all conditions hold; MATCH finds the first 1. In Excel 2019 and earlier, confirm with Ctrl+Shift+Enter. This works in every Excel version and extends to any number of criteria—just keep multiplying.
3. FILTER for all matches
The patterns above return the first match. When several rows qualify and you want them all, the FILTER function is the right tool:
=FILTER(C2:C500, (A2:A500=F2) * (B2:B500=G2), "No match")Results spill into as many cells as needed. Wrap it in SUM or wrap criteria into SUMIFS when what you actually want is a total of the matches rather than the values themselves—a surprising number of “multi-criteria lookup” questions are aggregation problems in disguise, where SUMPRODUCT also shines on older versions.
Habits That Keep Lookups Reliable
These apply to every function above:
- Lock your ranges. When you’ll fill a lookup formula down a column, the table reference needs absolute references (
$A$2:$D$500) or it shifts one row per fill and starts missing data near the bottom. Press F4 after selecting the range. - Prefer Tables and named ranges over A1 ranges.
=XLOOKUP(A2, Orders[ID], Orders[Total])is self-documenting and auto-expanding. - Validate inputs at the source. A drop-down built with data validation on the lookup-value cell eliminates the typo class of #N/A entirely.
- Check for duplicate keys before trusting results. Every single-result lookup silently returns the first match; match two columns and de-duplicate before reconciling.
- One lookup per row, not per cell, when pulling many fields. With XLOOKUP, return a multi-column array once and let it spill, instead of writing five near-identical formulas that each scan the table.
FAQ
Which lookup function should I learn first in 2026?
XLOOKUP, if your Excel version has it (365, 2021, or later). It’s exact-match by default, looks in any direction, and handles missing values without a wrapper function. Learn INDEX+MATCH second—you’ll need it whenever a workbook has to run on older Excel, and its two-MATCH form is still the cleanest matrix lookup.
Why does my VLOOKUP return the wrong value with no error?
Two usual causes. First, you omitted the fourth argument, so VLOOKUP ran in approximate-match mode on unsorted data—add FALSE. Second, someone inserted or deleted a column inside your table range, so your hard-coded column index now points at a different field. INDEX+MATCH and XLOOKUP are immune to the second problem because they reference the return column directly.
Can a lookup return every matching row instead of just the first?
Not with VLOOKUP, HLOOKUP, INDEX+MATCH, or a single XLOOKUP—all stop at one result. Use =FILTER(return_range, criteria_range=value) in modern Excel to spill all matches. In older versions the workaround is an array formula with SMALL and ROW, but at that point a PivotTable or Power Query is usually the saner tool.
Is INDEX+MATCH really faster than VLOOKUP?
On large datasets, yes, modestly: VLOOKUP loads the entire table range, while INDEX+MATCH scans one lookup column and reads one return cell. You’ll notice it above roughly 100,000 rows or with hundreds of lookup formulas recalculating. For raw speed on huge sorted data, a binary search (MATCH with type 1, or XLOOKUP with search_mode 2) is dramatically faster than any exact-match scan—but only on sorted data.
How do I do a case-sensitive lookup?
No lookup function is case-sensitive on its own—VLOOKUP("abc",…) happily matches “ABC”. Combine EXACT with INDEX and MATCH: =INDEX(B2:B500, MATCH(TRUE, EXACT(D2:D500, A2), 0)), entered with Ctrl+Shift+Enter in pre-2021 Excel. EXACT compares strings character-by-character including case, and MATCH finds the first TRUE.