SheetsdrillVLOOKUP
VLOOKUP(search_key, range, index, [is_sorted]) looks for a value in the first column of a range and returns the cell in the same row from the column you number. Pass FALSE as the fourth argument for an exact match; without it Google Sheets assumes the first column is sorted and can return the wrong row.
Syntax
=VLOOKUP(search_key, range, index, [is_sorted])| Part | What it is |
|---|---|
search_key | The value to find, for example a SKU in A2. |
range | The table to search. The search column must be its first column, for example Products!A:D. |
index | Which column of the range to return, counting from 1 at the first column. |
is_sorted | FALSE for exact match (almost always what you want). TRUE or omitted means approximate match on a sorted first column. |
Examples
=VLOOKUP(A2, Products!A:C, 3, FALSE)Result: The price (column C of the Products sheet) for the SKU in A2
=IFERROR(VLOOKUP(A2, Products!A:C, 3, FALSE), "Not found")Result: The price, or Not found when the SKU is missing
=VLOOKUP(B2, {0,"Low";1000,"Mid";5000,"High"}, 2, TRUE)Result: Low for 800, Mid for 2500, High for 7000
Approximate match returns the band whose threshold is the largest value not above B2.
Common mistakes
- #N/A with a value that is clearly in the table usually means trailing spaces or a number stored as text on one side; wrap in TRIM or VALUE, or check for an apostrophe before the number.
- Wrong values with no error almost always mean the fourth argument was omitted; add FALSE.
- #REF! because index is larger than the number of columns in the range; count the columns inside the range, not the sheet.
Practice VLOOKUP
These drills check your answer on live data, free, no account needed.
- Find the priceLook up the price of the SKU in F1 using VLOOKUP with an exact match. Answer in F2.
- Stock on handThe parts list is not sorted. Return the quantity on hand for the part in F1 with VLOOKUP. Answer in F2.
Questions
How do I VLOOKUP from another sheet or file?
Prefix the range with the sheet name (Prices!A:B) or wrap IMPORTRANGE: =VLOOKUP(A2, IMPORTRANGE(url, "Prices!A:B"), 2, FALSE).
Can VLOOKUP return multiple columns?
Yes with an array index: =ARRAYFORMULA(VLOOKUP(A2, Products!A:D, {2,3,4}, FALSE)) returns three cells.
Related
- XLOOKUPHow XLOOKUP works in Google Sheets: look up in any direction, set a not-found value, do approximate matches and search from the bottom, with common errors.
- INDEX and MATCHHow INDEX and MATCH work together in Google Sheets to look up values left or right, do two-way lookups by row and column, and find the last match.
- HLOOKUPHow HLOOKUP works in Google Sheets to search the first row of a range and return a value from a row below, with examples for month tables and common errors.
- IFERRORHow IFERROR in Google Sheets hides errors like #N/A, #DIV/0! and #VALUE! with a blank, a zero or a message, with examples for VLOOKUP and division.