XLOOKUP Function in Excel, Google Sheets, Airtable & Notion
XLOOKUP finds a value in one range and returns the value in the same position of another range. It matches exactly by default, can look in any direction and has a built-in value for “not found”.
Syntax in each app
| App | Support | Syntax & notes |
|---|---|---|
| Excel | ✔ Built in | XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])Excel 2021 / Microsoft 365 |
| Google Sheets | ✔ Built in | XLOOKUP(search_key, lookup_range, result_range, [missing_value], [match_mode], [search_mode]) |
| Airtable | ✘ Not available | — Airtable has no lookup functions. Link the two tables with a linked record field, then add a lookup field to pull the matching value (for example the Price of the linked Product). Formulas can only see the current record, so the link does the matching. |
| Notion | ≈ Workaround | prop("Products").filter(current.prop("Code") == "C-300").first().prop("Price")Use a relation and filter it. .find(current.prop("Code") == "C-300") also returns the first matching page. Matching is exact and case-sensitive; an empty result plays the role of if_not_found. |
| Excel | =XLOOKUP("C-300", A2:A6, B2:B6, "Not found") | open excel.new ↗ |
|---|---|---|
| Google Sheets | =XLOOKUP("C-300", A2:A6, B2:B6, "Not found") | open sheets.new ↗ |
| Airtable | ✘ No equivalent. Airtable has no lookup functions. Link the two tables with a linked record field, then add a lookup field to pull the matching value (for example the Price of the linked Product). Formulas can only see the current record, so the link does the matching. | |
| Notion workaround | prop("Products").filter(current.prop("Code") == "C-300").first().prop("Price") | |
Excel & Sheets use cell ranges. Paste this sample data into cell A1 of a blank sheet, then paste the formula into any empty cell.
Code Price A-100 4.5 B-200 12 C-300 7.25 D-400 30 E-500 18.9
The Notion formula assumes a relation property Products with Code and Price properties and always matches exactly; it returns empty when nothing matches.
Arguments
| Argument | What it means |
|---|---|
lookup_value | The value to find. |
lookup_array | The single row or column to search. |
return_array | The range to return from, same size as lookup_array. It can be to the left of the lookup column. |
[if_not_found] optional | What to return instead of #N/A when nothing matches. |
[match_mode] optional | 0 = exact (default), −1 = exact or next smaller, 1 = exact or next larger, 2 = wildcard match with * and ?. |
[search_mode] optional | 1 = first to last (default), −1 = last to first; 2 and −2 are binary searches on sorted data. |
Examples
| Formula | Result | Why | Copy |
|---|---|---|---|
=XLOOKUP("C-300", A2:A6, B2:B6) | 7.25 | Exact match is the default, no FALSE needed. | |
=XLOOKUP("Z-999", A2:A6, B2:B6, "Not found") | Not found | if_not_found replaces IFERROR. | |
=XLOOKUP(20, B2:B6, A2:A6, , -1) | E-500 | Looks left: the code of the largest price not above 20 (18.9). The data need not be sorted. | |
=XLOOKUP("B-200", A2:A6, B2:D6) | Three cells | A multi-column return_array spills the whole matching row. |
Examples use Excel / Google Sheets syntax unless noted.
How XLOOKUP works
XLOOKUP fixes almost every complaint about VLOOKUP. You point at the lookup column and the return column separately, so it can return values to the left, and inserting columns does not break it. It matches exactly unless you ask otherwise, and the fourth argument replaces the usual IFERROR(…, "Not found") wrapper.
The approximate modes are also safer: -1 (exact or next smaller) and 1 (exact or next larger) work on unsorted data because XLOOKUP scans every row. Wildcards only work with match_mode 2; in the default exact mode an asterisk is just an asterisk. If return_array has several columns, the result spills across cells, and you get #SPILL! if something is in the way.
XLOOKUP needs Excel 2021 or Microsoft 365; in Excel 2019 and older it shows #NAME?, so use INDEX + MATCH for files shared with older versions. Google Sheets supports it with the same arguments. Airtable has no lookup functions at all (use linked records and lookup fields), and Notion does lookups through relations: filter the related pages and take .first().
Frequently Asked Questions
- What is the difference between XLOOKUP and VLOOKUP?
- XLOOKUP matches exactly by default, can return columns to the left, does not break when columns are inserted and has a built-in not-found value. VLOOKUP works in every Excel version.
- Why do I get #NAME? with XLOOKUP?
- Your Excel version is older than Excel 2021. Use INDEX + MATCH instead.