VLOOKUP in Excel: Syntax, Examples & Common Errors
Updated 2026-10-12 · Free · Works in Excel & Google Sheets
Syntax
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])Quick answer
VLOOKUP searches the first column of a range for a lookup value and returns a value from another column in the same row. Use =VLOOKUP(A2, $D$2:$E$500, 2, FALSE) for an exact match — FALSE (or 0) is what you want 95% of the time. #N/A almost always means the value is not in the first column, or only looks identical because of text-number mismatches or invisible spaces.
Syntax
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])| Argument | Required | What it means |
|---|---|---|
| lookup_value | Yes | The value to search for — a number, text, date, or a cell reference like A2. |
| table_array | Yes | The range to search. VLOOKUP always looks in its FIRST column and can only return columns to the right. |
| col_index_num | Yes | Which column of table_array to return, counted from the left of the range (1 = the lookup column itself). |
| range_lookup | No | FALSE (or 0) = exact match. TRUE (or 1) = approximate match, which requires the first column sorted ascending. |
The first-column rule is the one that causes most #N/A errors: the lookup column must be the leftmost column of table_array, even if the data you actually want sits elsewhere in the sheet.
Basic Examples
=VLOOKUP(A2, $D$2:$E$500, 2, FALSE)Looks up the value in A2 within D2:E500 and returns the match from the second column. The $ signs lock the range so it stays put when you fill the formula down the column.
=VLOOKUP(A2, Sheet2!A:C, 3, FALSE)Same lookup across sheets: Sheet2!A:C is the range on the other sheet, and the third column of that range is returned.
=IFERROR(VLOOKUP(A2, $D$2:$E$500, 2, FALSE), "Not found")Wrapping VLOOKUP in IFERROR replaces the raw #N/A with a friendlier message — the standard pattern for customer-facing sheets.
Always add FALSE unless you truly need an approximate match. With TRUE (or an omitted fourth argument), VLOOKUP silently returns the nearest smaller value — wrong results with no error to warn you.
Common Errors and How to Fix Them
| Error | Most common cause | Fix |
|---|---|---|
| #N/A | The value is not in the first column of table_array — or it only looks identical (text vs. number, trailing spaces). | Check the first-column rule; convert text numbers with VALUE(); clean invisible spaces with a trim tool. |
| #REF! | col_index_num is larger than the number of columns inside table_array. | Count columns inside the range, not the whole sheet. |
| #VALUE! | col_index_num is less than 1 (often a broken reference), or the lookup text exceeds 255 characters in older Excel. | Use a column index of 1 or more; switch to INDEX/MATCH for very long strings. |
| #NAME? | Misspelled function name — or a localized name (BUSCARV, SVERWEIS, RECHERCHEV) used in an English Excel install. | Type =VLOOKUP( and let AutoComplete finish the name. |
Trailing spaces and non-breaking spaces pasted from websites are the sneakiest cause of #N/A: “NYC ” and “NYC” are different strings to VLOOKUP. A one-pass trim fixes the whole column before you lookup.
VLOOKUP vs. XLOOKUP
| VLOOKUP | XLOOKUP | |
|---|---|---|
| Available in | Every Excel version (and Google Sheets) | Microsoft 365 and Excel 2021+ |
| Looks left of the lookup column | No | Yes |
| Exact match by default | No — you must add FALSE | Yes |
| Survives column inserts | No — a static col_index_num can shift silently | Yes |
| When value is not found | #N/A (wrap in IFERROR) | Built-in if_not_found argument |
Stick with VLOOKUP for shared workbooks that must open anywhere, or when you work in Google Sheets. Choose XLOOKUP for new Microsoft 365 workbooks — it removes the first-column rule, the FALSE habit, and the IFERROR wrapper in one go.
VLOOKUP in Google Sheets
Google Sheets supports VLOOKUP with the same syntax and behavior as Excel — the formulas above work unchanged when pasted into a sheet.
- Exact and approximate match modes behave the same, including the sorted-first-column requirement for TRUE.
- Wildcards work identically: “?” matches one character, “*” any run of characters, and “~” escapes them.
- In locales that use a comma as the decimal separator (Germany, much of Europe and Latin America), type semicolons between arguments: =VLOOKUP(A2; D2:E500; 2; FALSE). Excel behaves the same way.
- Sheets has no XLOOKUP parity gap in modern accounts, but older shared spreadsheets may still rely on VLOOKUP — keep it for compatibility.
Limitations and Pro Tips
- Cannot look left: the lookup column is always the first column of the range. For a return column on the left, use INDEX/MATCH or XLOOKUP.
- Returns the first match only — duplicate keys in the lookup column are silently ignored.
- Lock the range with $ before filling down, or row 2 will look in D3:E501, row 3 in D4:E502, and so on.
- Approximate-match mode (TRUE) requires the first column sorted ascending, or results are wrong without any error.
- Double-click the fill handle (the small square at the selection corner) to copy the formula down to the last data row in one go.
Need to pull many columns from another file rather than one value? That is a table join, not a lookup — the free browser tools below do it without formulas.
Related Tools
Related Functions
Frequently Asked Questions
What does VLOOKUP do in Excel?
VLOOKUP (vertical lookup) searches the first column of a range for a value and returns whatever sits in another column of the same row. It is the classic way to pull a price, name, or status into a table from a larger lookup list.
Why does VLOOKUP return #N/A when I can see the value?
Usually the value only looks identical: one cell stores text and the other a real number, or the text contains trailing spaces or a non-breaking space. Convert text numbers with VALUE() or clean invisible whitespace, and the lookup will match.
Should I use TRUE or FALSE as the last argument?
Use FALSE (exact match) in almost every case. TRUE performs an approximate match that silently returns the nearest smaller value — and only works correctly when the first column is sorted ascending.
Can VLOOKUP look to the left?
No. VLOOKUP always searches the first column of the range and can only return columns to the right. To return a column on the left, use INDEX/MATCH — or XLOOKUP if you have Microsoft 365.
How do I VLOOKUP across two sheets?
Prefix the range with the sheet name: =VLOOKUP(A2, Sheet2!A:C, 3, FALSE). For another workbook, the reference becomes [Book.xlsx]Sheet2!A:C — though keeping both datasets in one workbook is more reliable.
Can VLOOKUP return multiple matches?
No. VLOOKUP stops at the first match. To return every match, use FILTER in Microsoft 365 or Google Sheets, or add helper columns that number the duplicates.
Does VLOOKUP work the same in Google Sheets?
Yes — the syntax, match modes, and wildcard support are identical to Excel. Only the argument separator changes in locales that use a comma as the decimal separator; type semicolons there instead.
Ready to try it on your own file?
Open Join Two Excel Tables by a Key Column — It's Free