VLOOKUP Function in Google Sheets

VLOOKUP in Google Sheets searches down the first column of a range and returns a value from any column in the same row. It is the most widely used lookup function in Sheets, and the one most people learn first.

You provide a search value, tell VLOOKUP where to look, and specify which column holds the result you want. VLOOKUP always searches the leftmost column of your range.

Parameters

Parameter	Required	Description
search_key	Yes	The value to search for in the first column of range. Can be a value, cell reference, or text string.
range	Yes	The range to search. VLOOKUP always searches the first column of this range and returns a value from a column you specify.
index	Yes	The column number within range to return a value from. The first column is 1, the second is 2, and so on.
is_sorted	No	FALSE for an exact match. TRUE (or omitted) for an approximate match on sorted data. Default is TRUE.

Examples

Exact match

Look up a product name and return its price from column 3:

=VLOOKUP("Widget", A2:C10, 3, FALSE)

This searches column A for “Widget” and returns the value from column C in the matching row. The FALSE argument ensures an exact match.

Approximate match for grade ranges

Return a letter grade based on a numeric score, where column A contains sorted score thresholds (0, 60, 70, 80, 90) and column B contains grades (F, D, C, B, A):

=VLOOKUP(85, A2:B6, 2, TRUE)

This returns “B” because 85 falls between the 80 and 90 thresholds. The TRUE argument requires that column A is sorted in ascending order.

Cell reference as search key

Use a cell value as the lookup term instead of hardcoding it:

=VLOOKUP(E2, A2:C100, 2, FALSE)

Whatever value is in E2 gets looked up in column A, and the result comes from column B.

Common Errors

#N/A --- The search key was not found in the first column of the range. Check for extra spaces, typos, or mismatched data types (text vs. number). Use =TRIM() on both the search key and lookup column to clean whitespace.

#REF! --- The index value is larger than the number of columns in range. If your range is A2:C10 (3 columns), the maximum index is 3.

#VALUE! --- The index argument is less than 1, or a non-numeric value was passed where a number is expected.

Tips

Set is_sorted to FALSE unless you specifically need approximate matching on sorted data. Using TRUE on unsorted data returns wrong results with no error or warning.

VLOOKUP only searches left to right. If your return column is to the left of your search column, use INDEX and MATCH instead.