XLOOKUP is the lookup function Excel should have shipped years ago. It searches in any direction, returns a friendly value when nothing matches, and can return several columns at once. For assignments, it is also a chance to show you understand why the newer function is safer than VLOOKUP.
These seven patterns cover the questions that appear most often in homework, tests and real reports.
The syntax
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
| Argument | What it does | Typical value |
|---|---|---|
lookup_value | The value to find | G2 |
lookup_array | The column or row to search | A2:A500 |
return_array | The column or row to return | D2:D500 |
if_not_found | What to show when there is no match | "Not found" |
match_mode | 0 exact, -1 exact or next smaller, 1 exact or next larger, 2 wildcard | 0 |
search_mode | 1 first-to-last, -1 last-to-first, 2 binary ascending, -2 binary descending | 1 |
The first three arguments are required. In most assignments you will use the first four and leave the last two at their defaults.
1. The basic exact lookup
=XLOOKUP(G2, A2:A500, D2:D500)
Find the ID in G2 inside A2:A500 and return the matching value from D2:D500. No column counting, no table-array lock, and the return column can sit to the left of the lookup column.
2. A friendly message instead of #N/A
=XLOOKUP(G2, A2:A500, D2:D500, "Not found")
The fourth argument replaces the error. This is cleaner than wrapping the whole formula in IFERROR, because it only handles a missing lookup — it does not hide a broken range or a syntax mistake.
3. Return several columns at once
=XLOOKUP(G2, A2:A500, B2:D500)
Point the return array at several columns and XLOOKUP spills the whole row. This replaces three separate VLOOKUPs and keeps the result consistent if the source columns move.
4. Look up horizontally
=XLOOKUP(H1, B1:Z1, B2:Z2)
The lookup array can be a row. This is the clean replacement for HLOOKUP when the header is in row 1 and the values are in row 2.
5. A wildcard match
=XLOOKUP("*"&G2&"*", A2:A500, D2:D500, "No match", 2)
Match mode 2 enables wildcards. The example finds the first cell that contains the text in G2. Use ? for a single character and ~* to match a literal asterisk.
6. A two-way lookup
Find a row by student name and a column by subject, then return the intersection:
=XLOOKUP(H1, B1:Z1, XLOOKUP(G2, A2:A100, B2:Z100))
The inner XLOOKUP returns the whole row for the student; the outer XLOOKUP picks the subject column. It looks complex, but each half is a normal lookup — which makes it easy to explain in a methodology note.
7. Approximate bands, such as grades or tax slabs
=XLOOKUP(B2, $F$2:$F$6, $G$2:$G$6, "No band", -1)
Match mode -1 finds an exact match or the next smaller value, so the lookup table must be sorted ascending. This is the modern replacement for VLOOKUP(..., TRUE).
XLOOKUP vs VLOOKUP: what to submit
| Job | VLOOKUP | XLOOKUP |
|---|---|---|
| Look left | Not possible | Yes |
| Missing value | Wrap in IFERROR | Built-in fourth argument |
| Insert a column | Breaks the column index | No index to break |
| Return several columns | One formula per column | One formula spills the row |
| Older Excel | Works everywhere | 365, 2021 and 2024 only |
If the brief says “use VLOOKUP”, submit VLOOKUP and add one XLOOKUP column labelled “robust alternative” with a one-line justification. That demonstrates judgment without ignoring the instruction. Read the full XLOOKUP vs VLOOKUP comparison for the version notes.
Why XLOOKUP returns an error
- #N/A: the value is genuinely missing, has trailing spaces, or is stored as text instead of a number. Use TRIM or check the data type.
- #VALUE!: the lookup and return arrays are different sizes, or the formula is inside a table that cannot spill.
- #REF!: a referenced sheet or range was deleted. Undo first, then re-point the formula.
- #SPILL!: a multi-column return has no empty space to expand into. Clear the cells to the right.
The free Excel Error Fixer explains each code, and the VLOOKUP/XLOOKUP Generator builds the formula from your ranges. For the classic function, read the VLOOKUP guide with five examples.
Need the lookup model built and explained? Send the workbook to an Excel expert for a fixed quote.
Before you submit, run the lookup formulas through the Workbook Health Check and the 23-point Assignment Readiness Check. Test a value that should not be found, a blank row and a duplicate key — three cases that catch most lookup errors.
