VLOOKUP Function in Google Sheets
Looks up a value in the first column of a range and returns a value from a column you specify in the same row. The original Google Sheets lookup function — still the most-used despite XLOOKUP arriving as a successor.
Syntax
VLOOKUP(search_key, range, index, [is_sorted])Returns: The value in the same row as search_key, taken from the column at index.
Excel equivalent: VLOOKUP (identical)
Parameters
| Name | Required | Description |
|---|---|---|
| search_key | Required | The value to search for in the first column of range. |
| range | Required | The range to consider. The first column is searched; the returned value comes from a column to the right. |
| index | Required | The column number in range to return the value from. 1 = first column, 2 = second column, and so on. |
| is_sorted | Optional | TRUE for approximate match on a sorted column (default). FALSE for exact match — almost always what you want. |
Examples
Look up a price by product name
=VLOOKUP("Apple", A2:C100, 3, FALSE)Searches column A for the text "Apple", returns whatever is in column C of that row. The FALSE is critical — without it VLOOKUP assumes the column is sorted and returns nearest match.
Output: Whatever price is listed for Apple.
Look up a value from another sheet
=VLOOKUP(A2, Prices!A:C, 3, FALSE)References the entire columns A:C on a sheet named "Prices". Use whole-column references when the lookup table grows over time so you don't have to update ranges.
Wrap with IFERROR for missing matches
=IFERROR(VLOOKUP(A2, Products!A:C, 3, FALSE), "Not found")VLOOKUP returns #N/A when the search_key isn't in the first column. Wrap with IFERROR to show a friendly fallback instead of an error code.
When to use an alternative
- XLOOKUP — You want a modern lookup that can search left or right and has a built-in if_not_found argument.
- INDEX + MATCH — You need to look up a value that appears to the left of the search column (VLOOKUP can only return from columns to the right).
- QUERY — You need SQL-style filtering, sorting, or aggregating on top of the lookup.
Common errors and how to fix them
#N/A
Cause: search_key not found in the first column of range, or trailing whitespace mismatch.
Fix: Wrap with IFERROR, or use TRIM() to clean both sides of the comparison.
#REF!
Cause: index is greater than the number of columns in range.
Fix: Check that range includes enough columns to reach the index you specified.
Returns wrong value
Cause: Omitted FALSE/0 for is_sorted, so VLOOKUP did an approximate match on unsorted data.
Fix: Add FALSE as the fourth argument unless you intentionally want approximate matching on sorted data.
Related functions
Frequently Asked Questions
What is the difference between VLOOKUP and XLOOKUP in Google Sheets?
XLOOKUP can search any column (VLOOKUP only searches the leftmost column of the range), can return values to the left of the search column, takes a built-in if_not_found argument so you don't need IFERROR, and defaults to exact match (VLOOKUP defaults to approximate). For new formulas, XLOOKUP is the better choice; VLOOKUP is still useful for compatibility with older sheets.
Why does my VLOOKUP return #N/A?
Three common causes: (1) the search_key truly isn't in the first column of range, (2) there's trailing whitespace on one side (the cell looks like "Apple" but actually contains "Apple "), (3) the column data types don't match (the cell shows 100 but is stored as text "100"). Use TRIM() and VALUE() to normalize, or wrap with IFERROR for a fallback message.
Can VLOOKUP look to the left?
No — VLOOKUP always returns from a column to the right of the search column. To look up a value to the left of the search column, use INDEX + MATCH or XLOOKUP.