📎 This article includes 3 downloadable practice files ↓

Looking things up is the single most common job in Excel: the price of a product, the manager of a region, the tax rate for an income slab. Excel has more than half a dozen ways to do it, and most people learn one (usually VLOOKUP) and stop there. This guide walks through every lookup technique you will realistically need, in the order you are likely to need them, with the traps that cause wrong answers.

Keep the Formula Lab open in another tab — it animates several of these formulas step by step.

1. The mental model: what every lookup does

Every lookup answers the same question: “Find this value in one list, then give me the matching value from another list.” So every lookup has three parts:

• The lookup value — what you are searching for (a product code in G2).
• The lookup range — where to search (the column of codes).
• The return range — what to bring back (the column of prices).

The functions differ only in how you describe those three parts, how they handle “not found”, and whether they match exactly or approximately. Once you see that, choosing is easy.

2. VLOOKUP: the classic

=VLOOKUP(G2, A2:D100, 4, FALSE)

Arguments: the value, the whole table, the column number to return (counting from the left of the table), and FALSE for an exact match.

Its three weaknesses

1. It only looks right. The lookup column must be the first column of the table.
2. The column number is hard-coded. Insert a column inside the table and 4 now points at the wrong data — silently.
3. The default is approximate match. Forget the FALSE and VLOOKUP assumes a sorted list, returning a nearby value instead of an error.

When approximate match is exactly right

Approximate match is perfect for bands — tax slabs, commission tiers, grades. Put the lower limit of each band in the first column, sorted ascending:

=VLOOKUP(G2, $A$2:$B$5, 2, TRUE)

An income of 850,000 falls between 700,000 and 1,000,000, so the formula returns 10%. The same idea works with XLOOKUP using match_mode -1 (“exact or next smaller”), which does not need the list sorted.

3. INDEX + MATCH: the flexible workhorse

=INDEX(D2:D100, MATCH(G2, A2:A100, 0))

MATCH returns the position of G2 in A2:A100 (e.g. 17). INDEX returns the 17th value of D2:D100. Because the lookup and return columns are separate, INDEX-MATCH can look left, survives inserted columns and works in every Excel version since the 1990s.

Two-way lookup (row and column)

=INDEX(B2:M50, MATCH(P2, A2:A50, 0), MATCH(Q1, B1:M1, 0))

The first MATCH finds the row (e.g. the cost centre), the second finds the column (e.g. the month). This is the go-to formula for pulling a single number out of a month-by-account report exported from Smart View or SAP.

4. XLOOKUP: the modern default

=XLOOKUP(G2, A2:A100, D2:D100, "Not found")

Available in Microsoft 365 and Excel 2021+. It fixes every VLOOKUP weakness: separate lookup and return ranges, exact match by default, and a built-in “if not found” argument. The optional fifth and sixth arguments add power:

=XLOOKUP("*"&G2&"*", B2:B100, D2:D100, "None", 2)     ' partial text match
=XLOOKUP(G2, A2:A100, B2:E100)                          ' returns 4 columns at once (spills)

5. Lookups with several conditions

=XLOOKUP(1, (A2:A100=G2)*(B2:B100=H2), D2:D100, "Not found")

Each comparison makes an array of TRUE/FALSE; multiplying them keeps only rows where all conditions are true. The same trick works inside MATCH for older Excel: =INDEX(D2:D100, MATCH(1, (A2:A100=G2)*(B2:B100=H2), 0)) (confirm with Ctrl+Shift+Enter in Excel 2019 and earlier). Full walkthrough: XLOOKUP with two criteria.

6. The last match, not the first

Latest price, current status, most recent payment: use XLOOKUP(…, , 0, -1), or in any version the famous =LOOKUP(2, 1/(A2:A100=G2), C2:C100). Explained here: the LOOKUP(2,1/…) trick.

7. All matches, not just one

=FILTER(D2:D100, A2:A100=G2, "None")                     ' spills every match down
=TEXTJOIN(", ", TRUE, FILTER(D2:D100, A2:A100=G2, ""))    ' all matches in one cell

More in return all matches in one cell.

8. HLOOKUP and XMATCH

HLOOKUP is VLOOKUP turned sideways — it searches the first row. XLOOKUP replaces it too. XMATCH is the modern MATCH: exact by default, with the same match and search modes as XLOOKUP. Use it inside INDEX when you want INDEX-MATCH behaviour with modern defaults.

9. Handling “not found” properly

• XLOOKUP: use the fourth argument — clean and specific.
• Others: IFNA(formula, "Not found") catches only #N/A. Prefer it to IFERROR, which also hides real mistakes such as #REF! from a deleted column.
• Reports: return "" (blank) or 0 only if a missing value truly means “nothing”, otherwise you hide data problems.

10. Why lookups fail when the value is “clearly there”

11. Speed on big workbooks

• Use exact-sized ranges or Excel Tables (Table1[Code]) instead of whole columns in heavy models.
• If the same row is needed for many columns, do the MATCH once in a helper column and use INDEX several times.
• Very large (100k+ rows)? A sorted list with binary search (XLOOKUP search_mode 2) is dramatically faster — but only if the data is really sorted.
• Power Query Merge is often a better answer than 100,000 lookup formulas.

12. Which lookup should you use?

FAQ

Is VLOOKUP obsolete?

No — it still works everywhere and is fine for quick checks. For files that will be maintained, INDEX-MATCH or XLOOKUP is safer.

Can XLOOKUP return a whole row?

Yes: give it a multi-column return range and the result spills across.

Why does my colleague see #NAME?

Their Excel does not have XLOOKUP. Send an INDEX-MATCH version.