Topic 05
XLOOKUP
XLOOKUP was built as a direct answer to VLOOKUP's three problems. Same job — find a value and return something from the same row — but every limitation has been removed. You'll use the same file and same Search sheet, this time filling the xlookup column.
Three Problems — Three Solutions
Each VLOOKUP limitation has a direct fix in XLOOKUP. The left side shows the problem; the right shows how XLOOKUP addresses it.
1
✓ Fixed
Search any column — not just the leftmost
VLOOKUP — Problem
The search column must be the first column of the table range. You cannot search by name if ID is column 1.
XLOOKUP — Solution
You provide the search column and the return column separately and independently. Search in any column, return from any column — in any order, even to the left.
=XLOOKUP($G$3, table1[ID], table1[name])
=XLOOKUP($G$3, table1[name], table1[Salary])
2
✓ Fixed
Return by column name — not by index number
VLOOKUP — Problem
You must count columns manually and type a number. Insert a column anywhere and every formula silently breaks — no error, wrong results.
What is column 7? You have to count.
=VLOOKUP($G$3, table1, 7, 0)
What is column 7? You have to count.
XLOOKUP — Solution
You reference the return column by name. Columns can be reordered, inserted, or deleted — the formula always points to the right place.
Self-documenting. No counting.
=XLOOKUP($G$3, table1[ID], table1[Hire Date])
Self-documenting. No counting.
3
✓ Fixed
Exact match by default — no forgotten argument
VLOOKUP — Problem
The 4th argument defaults to approximate match (
Dangerous — approximate match active.
TRUE). Forgetting to add 0 returns wrong results with no warning.
=VLOOKUP($G$3, table1, 2)
Dangerous — approximate match active.
XLOOKUP — Solution
Exact match is the default. Three arguments are all you need for a standard lookup — no 4th argument required.
Safe by default. Clean and readable.
=XLOOKUP($G$3, table1[ID], table1[name])
Safe by default. Clean and readable.
XLOOKUP Syntax
= XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
lookup_value
The known value to search for. Lock it with
$G$3 so it stays fixed when you copy the formula down.
lookup_array
The column to search in. Just this one column — not the whole table. In our case:
table1[ID].
return_array
The column to return from. Referenced by name —
table1[name], table1[Salary], etc. This is what replaces the column index number.
[if_not_found]
Optional. What to display when no match is found. Useful to avoid the
#N/A error — e.g. "Not found". If omitted, Excel returns #N/A.
[match_mode]
Optional.
0 = exact match (default — no need to write it). -1 = exact or next smaller. 1 = exact or next larger. Used in VLOOKUP approximate topics.
[search_mode]
Optional.
1 = first to last (default). -1 = last to first. Useful when you want the most recent match.
3 arguments is all you need
For everyday lookups, you only need the first three arguments. The rest are optional and only needed for advanced scenarios. Compare this to VLOOKUP's mandatory 4th argument that caused Problem 3.
Training Exercise
Two exercises this topic. First, rewrite the three students formulas from Topic 04 — replacing the column numbers with column names. Then fill the XLOOKUP column in lookup.xlsx.
Quick Callback — students.xlsx
In Topic 04 you wrote VLOOKUP(A6, students, 3, 0), ...7, 0, and ...8, 0). Rewrite all three using XLOOKUP — no column numbers, just column names.
| Field | VLOOKUP (Topic 04) | XLOOKUP (now) |
|---|---|---|
| Name | VLOOKUP(A6, students, 3, 0) |
=XLOOKUP(A6, students[ID], students[First Name]) |
| GPA | VLOOKUP(A6, students, 7, 0) |
=XLOOKUP(A6, students[ID], students[GPA]) |
| Fees | VLOOKUP(A6, students, 8, 0) |
=XLOOKUP(A6, students[ID], students[Fees]) |
Now open lookup.xlsx and go to the Search sheet. The vlookup column is already filled from Topic 04. Now fill the xlookup column for all 10 fields. Compare both columns side by side — they should return identical results for every selected ID.
Download lookup.xlsx| Field | XLOOKUP Formula |
|---|---|
| ID | =XLOOKUP($G$3, table1[ID], table1[ID]) |
| name | =XLOOKUP($G$3, table1[ID], table1[name]) |
| Building | =XLOOKUP($G$3, table1[ID], table1[Building]) |
| Department | =XLOOKUP($G$3, table1[ID], table1[Department]) |
| Phone | =XLOOKUP($G$3, table1[ID], table1[Phone]) |
| Status | =XLOOKUP($G$3, table1[ID], table1[Status]) |
| Hire Date | =XLOOKUP($G$3, table1[ID], table1[Hire Date]) |
| Years | =XLOOKUP($G$3, table1[ID], table1[Years]) |
| Benefits | =XLOOKUP($G$3, table1[ID], table1[Benefits]) |
| Salary | =XLOOKUP($G$3, table1[ID], table1[Salary]) |
Notice what changed — and what didn't
Every formula has the same
$G$3 and table1[ID]. Only the return column name changes — no counting, no numbers. If the HR List gains a new column tomorrow, not a single formula breaks.
Important Limitation
Both VLOOKUP and XLOOKUP return only the first match
If your data has duplicate values in the search column, VLOOKUP and XLOOKUP will always return the result from the first matching row — and silently ignore every other match. There is no error, no warning. You simply never see the second, third, or any subsequent result.
This applies to both functions equally. XLOOKUP's
This applies to both functions equally. XLOOKUP's
[search_mode] argument lets you choose between first-to-last or last-to-first — but it still returns only one result.
When you have duplicates and need all matching rows — that's exactly what FILTER (Topic 07) is for. It returns every row that matches, not just the first one.