XLOOKUP - Retrieve an aligned value in either direction
Find an employee's pay from an ID, return a field to the left, and choose how missing or approximate matches should behave.
By the end, you can
- Separate lookup and return arrays.
- Use exact matching and a missing-ID result.
- Distinguish Excel match modes from the Practice subset.
Before you start: Read cell ranges and absolute references. IFERROR explains broader error handling.
In this lesson
Find the record, then return its field
XLOOKUP searches a row or column of keys and returns the aligned value from another row or column. Payroll can find pay by employee ID without moving the name column. Return data can be on either side of the key column. For an ordinary lookup, Excel uses an exact match and searches from the first entry by default.
Syntax and arguments
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])Use aligned lookup and return arrays. Optional arguments control missing matches, matching rules, and search direction.
| Argument | Purpose | Example |
|---|---|---|
lookup_valueRequired | The key to find. | D2 |
lookup_arrayRequired | One row or column containing keys. | B2:B4 |
return_arrayRequired | Aligned values to return; Excel also supports multi-column returns. | C2:C4 |
if_not_foundOptional | An explicit result for an absent match; omitted means #N/A. | "Unknown ID" |
match_modeOptional | 0 exact (default), -1 exact/next smaller, 1 exact/next larger, 2 wildcard. | -1 |
search_modeOptional | 1 forward (default), -1 reverse, 2 ascending binary, -2 descending binary. | -1 |
Payroll requests from an ID list
₹51,000| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Employee | ID | Monthly pay | Requested ID | Pay or message |
| 2 | Anika | E101 | ₹42,000 | E103 | ₹51,000 |
| 3 | Dev | E102 | ₹38,000 | E101 | ₹42,000 |
| 4 | Meera | E103 | ₹51,000 | E999 | Unknown ID |
Return left and handle missing IDs
=XLOOKUP("E102",B2:B4,A2:A4)Search IDs in column B and return Dev from column A; no column rearrangement is needed.
=XLOOKUP("E999",B2:B4,C2:C4,"Unknown ID")The key is absent. if_not_found replaces this missing-match result, not unrelated formula errors.
Excel can match a band instead of an ID
0| A | B | |
|---|---|---|
| 1 | Minimum pay INR | Rate |
| 2 | 0 | 0% |
| 3 | 30000 | 5% |
| 4 | 50000 | 10% |
=XLOOKUP(45000,A2:A4,B2:B4,"No band",-1)There is no 45,000 boundary. Match mode -1 selects 30,000 and returns its 5% rate.
Reverse search (-1) finds the last matching record instead of the first, which is useful for a dated history kept in order. Binary search modes require the stated ascending or descending sort order; unsorted data can give incorrect answers. Do not apply band logic to IDs.
Limits, compatibility, and mistakes
- A lookup returns one match by default, not a total over duplicate keys. Check whether IDs are unique; a first match can conceal duplicate records.
- The default exact linear search does not require sorted keys. Wildcards need match_mode 2; * and ? are patterns and ~ escapes them.
- A matched blank return cell commonly displays as zero; if_not_found is for an absent key, not an empty field. Clean unexpected spaces and align the ranges.
- XLOOKUP is available in Microsoft 365 and Excel 2021/2024, but not Excel 2016 or 2019.
Check your understanding
Choose an answer for instant feedback. You can try again; answers stay only on this page.