2.2Lookup: VLOOKUP, XLOOKUP, INDEX/MATCH
The essentials in 30 seconds
- Lookup = key → value: you know a code, you retrieve the aligned price or rate — never manual copying.
- XLOOKUP first: searches anywhere, returns anywhere, handles absence — VLOOKUP only sees to the right.
- Handle the unfindable: 4th argument of XLOOKUP for the message — a raw #N/A is never a result.
Running mini-case — Wassim and the missing VAT rate
Wassim (2.1) validates his invoices, but each line carries a VAT code whose rate he must find in a reference table — he copied it by hand, with errors. He writes his first XLOOKUP: the code in A2, searched in the Code column of the table, rate returned from the Rate column, "To check" if the code is absent. When he inserts a column in the table, his formula still holds — where his colleague's old VLOOKUP by number breaks. In 2.3, Wassim will clean the codes before looking them up; in 2.5, his table will become a structured table with named columns.
Continuation of the 2.1 thread: Wassim's grid retrieves its rates automatically — and continues in 2.3, 2.4 and 2.5.
1. The need: crossing two tables
You often have a key (a VAT code, a product code) and want to retrieve the associated info from a reference table (the rate, the price). This is the lookup: «for this key, give me the corresponding value». Wassim crosses his invoice lines with his rate table: a copied formula replaces dozens of manual copies, without typos.
2. XLOOKUP, the modern one
=XLOOKUP(key, lookup_range, result_range) searches the key in a column and returns the aligned value from another — regardless of the column order. E.g. =XLOOKUP(A2, VATRates[Code], VATRates[Rate], "To check"). The 4th argument displays a clean message when the key is absent, instead of the raw #N/A. Wassim uses it to flag unknown VAT codes to check with the supplier.
3. VLOOKUP (old) & INDEX/MATCH
VLOOKUP searches only in the 1st column and returns a column to the right by its number: =VLOOKUP(A2, $E$2:$G$50, 3, FALSE) — fragile, because inserting a column shifts the number. INDEX/MATCH is the old flexible combination: MATCH finds the position of the key, INDEX returns the value at that position: =INDEX($G$2:$G$50, MATCH(A2, $E$2:$E$50, 0)). Today, XLOOKUP advantageously replaces both when available (Microsoft 365 / 2021+).
Did you know?
Naming the columns is insuring the lookup.
🔎 Concrete example — 🔎 Concrete example — Wassim's VAT rate
Table VATRates (Code, Label, Rate); code entered in A2:
=XLOOKUP(A2, VATRates[Code], VATRates[Rate], "To check")
compared (old world):
=VLOOKUP(A2, $E$2:$G$50, 3, FALSE)
=INDEX($G$2:$G$50, MATCH(A2, $E$2:$E$50, 0))Reading: the columns are named (structured table, see 2.5) → a readable formula, insensitive to insertions.
Lesson: the 4th argument turns the #N/A error into an action instruction: «To check».
Section vocabulary
- Lookup key
- The known value (VAT code, product code) used to find the associated information.
Simple example: Wassim looks for the VAT code entered in A2.
What it is not: the unknown value we want to retrieve.
Reflex: a clean, homogeneous key first.
- XLOOKUP
- Searches a key in a range and returns the aligned value from another range, with a message if not found.
Simple example: Wassim returns the rate from VATRates[Rate].
What it is not: a lookup limited to the right by number.
Reflex: first choice if available.
- VLOOKUP
- Searches in the 1st column of a table and returns the n-th column to the right; FALSE = exact lookup.
Simple example: Wassim's colleague uses =VLOOKUP(A2,$E$2:$G$50,3,FALSE).
What it is not: a lookup in any direction.
Reflex: always FALSE at the end of the formula.
- INDEX / MATCH
- MATCH finds the position of a key, INDEX returns the value at that position: the historic flexible combination.
Simple example: Wassim rescues an old workstation with =INDEX($G$2:$G$50,MATCH(A2,$E$2:$E$50,0)).
What it is not: a function that sorts or filters.
Reflex: position first, value next.
In practice — In practice — Crossing two tables
- Create a table VATRates (Code, Label, Rate) converted into a structured table.
- In another area, enter a code and retrieve its rate with XLOOKUP.
- Add the 4th argument to display «To check» if the code does not exist.
- Insert a column in the table and check that XLOOKUP still holds (unlike VLOOKUP by number).
Ready-to-say script — justifying XLOOKUP (30 s)
« Before, I copied each rate by hand and made mistakes. Now the formula looks for the code in the table and returns the aligned rate, even if a column is inserted. And if the code is unknown, it displays to check instead of a raw error. Zero copying, zero typos. »
Say it while showing Wassim's rate column filling itself.
Copy-paste template — robust lookup
=XLOOKUP(A2, VATRates[Code], VATRates[Rate], "To check")
=INDEX($G$2:$G$50, MATCH(A2, $E$2:$E$50, 0))Missing code
A new VAT code returns an error: Wassim had planned the 4th argument, he reads «To check» and calls the supplier.
Inserted column
The colleague inserts a column and his VLOOKUP returns the label instead of the rate: Wassim, with named XLOOKUP, does not move.
Old Excel
A workstation without XLOOKUP: Wassim rescues it with INDEX/MATCH, waiting for the update to Microsoft 365.
Dirty key
Two «identical» codes are not found: invisible spaces — Wassim cleans with TRIM (see 2.3).
✓ Best practices
- Prefer XLOOKUP (flexible, robust, handles absence).
- Always FALSE (exact) in VLOOKUP, never an approximate lookup by default.
- Look up on structured tables with named columns.
✗ Common pitfalls
- VLOOKUP by column number → breaks if a column is inserted.
- Forgetting to handle #N/A (message with the 4th argument).
- Looking up on a key of a different type (text «12» vs number 12).
Key takeaways
- Lookup = finding the value associated with a key.
- XLOOKUP: searches anywhere, returns anywhere, handles absence.
- VLOOKUP (1st column, number to the right, FALSE) is more fragile.
- INDEX/MATCH = flexible alternative on versions without XLOOKUP.
48h challenge — apply tomorrow
On your reference table (rates, prices, scale): a named XLOOKUP copied over 10 rows, the 4th argument filled in, then insert a column and see that everything holds.
- I can find a value by its key with XLOOKUP on named columns.
- I can cleanly handle absent keys (4th argument).
- I know VLOOKUP (FALSE) and INDEX/MATCH for constrained cases.
Frequently asked questions
I don't have XLOOKUP, what should I do?
XLOOKUP requires Microsoft 365 / 2021+. On an older version, use INDEX/MATCH (flexible, searches anywhere) or failing that VLOOKUP with FALSE — but beware of the column number that breaks on insertion.
Why do I get #N/A when the key visibly exists?
Often stray spaces or different types (the text «12» is not the number 12). Clean with TRIM (see 2.3), harmonize the type, or display a message via the 4th argument of XLOOKUP.
Which lookup function is the most robust to column insertions?