VLOOKUP in Google Sheets
Use VLOOKUP to find a value in the first column of a range and return something from a column to its right — a price for a product, a name for an ID, and so on.
=VLOOKUP(A2, Sheet2!A:C, 3, FALSE)
Finds A2 in the first column of Sheet2!A:C and returns the value from the 3rd column. FALSE forces an exact match.
How it works
VLOOKUP takes four things: the search key, the range to search, the column index (counted from the left of the range, starting at 1) to return, and whether the match is sorted. Always pass FALSE as the last argument for an exact match — TRUE assumes the first column is sorted ascending and can return the wrong row.
Variations
Exact match on the same sheet
=VLOOKUP(A2, $E$2:$F$100, 2, FALSE)
Locks the lookup table with $ so it does not shift when you fill the formula down.
Return "not found" instead of #N/A
=IFERROR(VLOOKUP(A2, E:F, 2, FALSE), "not found")
Wrap in IFERROR so missing keys show a friendly message.
Approximate match (find a tier/bracket)
=VLOOKUP(A2, $E$2:$F$10, 2, TRUE)
The lookup column must be sorted ascending; returns the largest value <= A2. Good for tax brackets and grade scales.
Examples
| Scenario | Formula |
|---|---|
| Look up a price by product name | =VLOOKUP(A2, Products!A:B, 2, FALSE) |
| Look up from another workbook | =VLOOKUP(A2, IMPORTRANGE("URL","Sheet1!A:C"), 3, FALSE) |
| Case-insensitive by design | =VLOOKUP(A2, E:F, 2, FALSE) |
FAQ
Why does VLOOKUP only look right?
VLOOKUP always searches the leftmost column of the range and returns a column to its right. To look left, use INDEX/MATCH or XLOOKUP instead.
What does FALSE mean in VLOOKUP?
FALSE (or 0) means exact match. TRUE (or 1) means approximate match and requires the first column to be sorted ascending.
How do I fix #N/A from VLOOKUP?
It means the key was not found. Check for extra spaces (wrap the key in TRIM), make sure the search column is the first column of the range, and wrap the formula in IFERROR to show a message.