28 min read
XLOOKUP and VLOOKUP Basics
Look up a department's Q3 budget from a table. XLOOKUP is flexible; VLOOKUP remains common in older workbooks.
Why Look Up Instead of Scroll
Budget summaries often pull one figure — "What is Humanities Q3?" — without scrolling the main grid.
James Okonkwo built a Summary sheet for Northgate Academy governors. One cell showed each department's Q3 spend via lookup — update the grid and the summary refreshed automatically.
On Budgets, columns A:E are your lookup table. You will write lookups on Summary or spare columns M:P.
Budget Table as Lookup Source
| Department budgets | ||||
|---|---|---|---|---|
XLOOKUP Syntax
XLOOKUP finds a value in one column and returns a value from another.
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found])
Example — Humanities Q3:
=XLOOKUP("Humanities",A2:A6,D2:D6)
| Argument | Here |
|---|---|
| lookup_value | "Humanities" |
| lookup_array | A2:A6 (department names) |
| return_array | D2:D6 (Q3 amounts) |
Result: 10800
Build XLOOKUP on the Sheet
In O1, type Department; P1 type Q3 lookup. In O2, type Humanities. In P2, type =XLOOKUP(O2,A2:A6,D2:D6). Press Enter. P2 shows 10800.
Change O2 to Science — P2 updates to 18200.
=XLOOKUP("Sci",A2:A6,D2:D6) returns #N/A! — no exact match. Department names must match Expenses and Budgets spelling: Science, not Sci.
VLOOKUP — Column Index
VLOOKUP searches the leftmost column of a table and returns a value from a column to the right.
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
=VLOOKUP("Science",A2:E6,4,FALSE)
| Part | Meaning |
|---|---|
| A2:E6 | Table — lookup column must be leftmost |
| 4 | Return column 4 within the table (= Q3, column D) |
| FALSE | Exact match — required for department names |
Q3 is the 4th column of A:E (A=1, B=2, C=3, D=4).
Always use FALSE (or 0) for exact text lookups on department names. Approximate match may return the wrong row.
Handle Missing Departments
XLOOKUP optional fourth argument:
=XLOOKUP(O2,A2:A6,D2:D6,"Not found")
VLOOKUP alternative: wrap in IFERROR:
=IFERROR(VLOOKUP(O2,A2:E6,4,FALSE),"Not found")
Useful when a dropdown lists departments including PE — not yet in A2:A6.
XLOOKUP vs VLOOKUP
| XLOOKUP | VLOOKUP |
|---|---|
| Return column can be anywhere | Return column must be right of lookup column |
| Separate lookup and return arrays | Single table with column index |
| Excel 2021 / M365 | Available in older workbooks |
| Built-in if_not_found argument | Often needs IFERROR wrapper |
Practice
Swipe or use the arrows to move between questions.
1 / 2
More lessons in Microsoft Excel · Next: Creating and Using Named Ranges · Previous: Nested IF and the IFS Function