Spreadsheet formulas explained, side by side

VLOOKUP Function in Excel, Google Sheets, Airtable & Notion

VLOOKUP searches the first column of a table for a value and returns the value from another column in the same row. Always pass FALSE as the last argument unless you really want an approximate match.

Syntax in each app

AppSupportSyntax & notes
Excel✔ Built inVLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
Google Sheets✔ Built inVLOOKUP(search_key, range, index, [is_sorted])
The last argument is called is_sorted but works the same: FALSE = exact match.
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") == "B-200").first().prop("Price")
Notion has no VLOOKUP. Add a relation to the database that holds the table, then filter the related pages in a formula. == is case-sensitive, and the formula returns empty instead of #N/A when nothing matches.
Try VLOOKUP with your own values
Try b-200 (case does not matter) or C* (wildcard)
One item per line
One value per line, or separate with commas
1 = code, 2 = price
Result (as Excel & Google Sheets return it)
12
Your formula, ready to paste
Excel=VLOOKUP("B-200", A2:B6, 2, FALSE) open excel.new ↗
Google Sheets=VLOOKUP("B-200", A2:B6, 2, FALSE) 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") == "B-200").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 to a database with Code and Price properties, and always matches exactly. With TRUE, unsorted codes give unreliable results in Excel and Sheets too.

Arguments

ArgumentWhat it means
lookup_valueThe value to find in the first column of the table. Text is matched without regard to case; with an exact match, * and ? work as wildcards.
table_arrayThe table to search, e.g. A2:B6. The lookup column must be its leftmost column.
col_index_numWhich column of the table to return: 1 is the lookup column itself, 2 the next one, and so on. Larger than the table gives #REF!.
[range_lookup] optionalFALSE for an exact match. TRUE or omitted = approximate match, which needs the first column sorted ascending.

Examples

FormulaResultWhyCopy
=VLOOKUP("B-200", A2:B6, 2, FALSE)12With the tester's sample data: the price in column 2 of the row whose code is B-200.
=VLOOKUP("c*", A2:B6, 2, FALSE)7.25Wildcards work in exact mode; case is ignored.
=VLOOKUP(72, {0,"F";60,"D";70,"C";80,"B";90,"A"}, 2, TRUE)CApproximate match on a sorted band table: the largest threshold not above 72 is 70.
=IFERROR(VLOOKUP("Z-999", A2:B6, 2, FALSE), "Not found")Not foundReplace #N/A with your own text.

Examples use Excel / Google Sheets syntax unless noted.

How VLOOKUP works

VLOOKUP is the classic way to pull a value from a reference table: a price by product code, a name by employee ID. It always searches the leftmost column of the range and returns a value from a column to its right. It cannot look left; for that use INDEX + MATCH or XLOOKUP.

The biggest trap is the last argument. If you leave it out, VLOOKUP does an approximate match, which assumes the first column is sorted and quietly returns the nearest smaller value instead of an error. On unsorted data that means wrong answers that look right. Always type FALSE (or 0) unless you are matching against sorted bands such as tax brackets or grade thresholds. The second trap is the hard-coded column number: insert a column inside the table and 3 now points at the wrong column. INDEX + MATCH and XLOOKUP refer to the return column directly, so they survive inserted columns.

When nothing matches you get #N/A. Wrap the formula in IFNA or IFERROR to show a friendlier result, and check for stray spaces (fix with TRIM) or numbers stored as text when a value you can see is not found. Airtable and Notion have no VLOOKUP: in Airtable a linked record field plus a lookup field does the job, and in Notion a relation plus a formula such as prop("Products").filter(current.prop("Code") == "B-200").first().prop("Price").

Frequently Asked Questions

Why does VLOOKUP return #N/A when the value is clearly there?
Usually extra spaces, or a number stored as text in one place and as a number in the other. Compare with =A2=E2, clean with TRIM, or convert with VALUE.
Why does VLOOKUP return the wrong row?
The last argument is missing or TRUE, so it did an approximate match on unsorted data. Add FALSE.
Can VLOOKUP return a value to the left of the lookup column?
No. Use INDEX + MATCH or XLOOKUP.

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.