VLOOKUP is one of the most searched Google Sheets functions, and it does one thing: look up a value in the first column of a range, then return a value from a different column in the same row.
The typical use case: you have a product ID in one place and a price list in another. VLOOKUP connects them without manual copy-pasting.
This tutorial covers the full syntax, common errors, and when to use XLOOKUP instead.
---
The VLOOKUP Syntax
=VLOOKUP(search_key, range, index, [is_sorted])Four arguments:
| Argument | What it means | |----------|--------------| | search_key | The value you're looking up — a cell reference, a number, or text in quotes | | range | The table to search. The first column of this range must contain the values you're matching against. | | index | Which column number to return a value from. 1 = first column, 2 = second column, and so on. | | is_sorted | Use FALSE for exact matches (almost always what you want). Use TRUE only for approximate matches in sorted data. |
---
A Simple Example
You have a price list on a separate tab (called Products):
| A | B | C | |---|---|---| | Product ID | Product Name | Price | | P001 | Laptop Stand | 49.00 | | P002 | Keyboard | 89.00 | | P003 | Monitor | 249.00 |
On your order sheet, you have product IDs in column A and want to pull in the price automatically.
In cell B2 of the order sheet:
=VLOOKUP(A2, Products!$A:$C, 3, FALSE)Plain English: look for the value in A2, search in columns A through C of the Products tab, return the value from the 3rd column (Price), and use an exact match.
The $ signs lock the range — when you copy the formula down, it always searches the same table.
---
Exact vs Approximate Matching
Always use FALSE as the fourth argument unless you specifically need approximate matching.
FALSE (exact match): VLOOKUP looks for a value that exactly matches the search key. If the value isn't found, it returns #N/A. This is what you want 95% of the time.
TRUE (approximate match): VLOOKUP finds the largest value less than or equal to the search key. Requires the first column of your range to be sorted in ascending order. Used for tiered pricing, tax brackets, grade scales — situations where you want "find the closest value without going over."
Example of approximate matching for a discount tier:
| Min Order | Discount | |-----------|----------| | 0 | 0% | | 100 | 5% | | 500 | 10% | | 1000 | 15% |
=VLOOKUP(B2, $E:$F, 2, TRUE)If B2 is 350, VLOOKUP returns 5% (the 100 tier — the largest value ≤ 350).
---
Common Errors
#N/A
The search key wasn't found in the first column of your range. Check:
- Is the value actually in the lookup column?
- Are there extra spaces? (Use
=TRIM()to clean them.) - Is the data type consistent? A number stored as text won't match a real number.
#REF!
Your index number is larger than the number of columns in your range. If your range is A:C (3 columns) and you used index 4, you'll get #REF!.
Wrong value returned
Check that you used FALSE for the fourth argument. Using TRUE on unsorted data returns unpredictable results.
VLOOKUP only returns the first match
VLOOKUP stops at the first matching value. If your lookup column has duplicates, it returns data for the first match only. For multiple matches, use FILTER or QUERY instead.
---
VLOOKUP vs XLOOKUP
XLOOKUP is a newer function that fixes several VLOOKUP limitations:
| | VLOOKUP | XLOOKUP | |--|---------|---------| | Lookup direction | Left-to-right only | Any direction | | Return column must be to the right | Yes | No | | If value not found | #N/A (or use IFERROR) | Customizable fallback | | Multiple return columns | No | Yes | | Exact match default | No (must specify FALSE) | Yes |
If you're on a newer Google Sheets, XLOOKUP is usually the better choice. VLOOKUP is still worth knowing because it's in millions of existing spreadsheets and the syntax shows up in documentation and Stack Overflow answers constantly.
A direct XLOOKUP equivalent for the example above:
=XLOOKUP(A2, Products!$A:$A, Products!$C:$C)---
Wrapping VLOOKUP in IFERROR
When the search key might not exist in your table, #N/A errors break the look of the sheet. Wrap the formula in IFERROR to return a blank or a custom message instead:
=IFERROR(VLOOKUP(A2, Products!$A:$C, 3, FALSE), "")Returns blank if not found. Replace "" with "Not found" or 0 depending on what your sheet needs.
---
Common Problems
VLOOKUP only looks right. The search column must be the leftmost column of your range. If you want to look up a value and return something to its left, use INDEX/MATCH or XLOOKUP instead.
Adding a column breaks your formula. If you insert a column into your lookup table, the index number shifts. Use MATCH to make the column reference dynamic, or switch to XLOOKUP which uses a column reference instead of a number.
Case sensitivity. VLOOKUP is case-insensitive — "laptop" and "Laptop" are treated as the same. If case matters, use EXACT inside an ARRAYFORMULA.
---
What to Build on Top of This
- INDEX/MATCH — a more flexible alternative to VLOOKUP. [How to Use INDEX/MATCH in Google Sheets →]
- XLOOKUP — the modern replacement. [How to Use XLOOKUP in Google Sheets →]
- IMPORTRANGE + VLOOKUP — pull data from another spreadsheet and look up values in it. How to Use IMPORTRANGE in Google Sheets
Don't want to set this up yourself? Describe your lookup scenario and it'll be configured for your sheet. Get it installed