Spreadsheet formulas explained, side by side

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

AppSupportSyntax & notes
Excel✔ Built inXLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
Excel 2021 / Microsoft 365
Google Sheets✔ Built inXLOOKUP(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≈ Workaroundprop("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.
Try XLOOKUP with your own values
One item per line
One value per line, or separate with commas
Leave empty to get #N/A
Result (as Excel & Google Sheets return it)
7.25
Your formula, ready to paste
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

ArgumentWhat it means
lookup_valueThe value to find.
lookup_arrayThe single row or column to search.
return_arrayThe range to return from, same size as lookup_array. It can be to the left of the lookup column.
[if_not_found] optionalWhat to return instead of #N/A when nothing matches.
[match_mode] optional0 = exact (default), −1 = exact or next smaller, 1 = exact or next larger, 2 = wildcard match with * and ?.
[search_mode] optional1 = first to last (default), −1 = last to first; 2 and −2 are binary searches on sorted data.

Examples

FormulaResultWhyCopy
=XLOOKUP("C-300", A2:A6, B2:B6)7.25Exact match is the default, no FALSE needed.
=XLOOKUP("Z-999", A2:A6, B2:B6, "Not found")Not foundif_not_found replaces IFERROR.
=XLOOKUP(20, B2:B6, A2:A6, , -1)E-500Looks 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 cellsA 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.

Part of the Lookup & Reference functions. Results in the tester follow Excel and Google Sheets behaviour; always double-check important work in your own file.