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
| App | Support | Syntax & notes |
|---|---|---|
| Excel | ✔ Built in | VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]) |
| Google Sheets | ✔ Built in | VLOOKUP(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 | ≈ Workaround | prop("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. |
| 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
| Argument | What it means |
|---|---|
lookup_value | The 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_array | The table to search, e.g. A2:B6. The lookup column must be its leftmost column. |
col_index_num | Which 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] optional | FALSE for an exact match. TRUE or omitted = approximate match, which needs the first column sorted ascending. |
Examples
| Formula | Result | Why | Copy |
|---|---|---|---|
=VLOOKUP("B-200", A2:B6, 2, FALSE) | 12 | With 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.25 | Wildcards work in exact mode; case is ignored. | |
=VLOOKUP(72, {0,"F";60,"D";70,"C";80,"B";90,"A"}, 2, TRUE) | C | Approximate 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 found | Replace #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.