Looking things up with VLOOKUP
10 minutes · 4 steps · free, no sign-up
VLOOKUP finds a value in a table by its key, like looking up a product code to get its name and price. It is one of the most useful and most feared functions, so we take it slowly.
- Read the four parts of VLOOKUP
- Return different columns by changing the index
- Use FALSE for an exact match
- Hide #N/A errors with IFERROR
Click a cell and start typing. Enter edits, Tab moves right, arrows move, Delete clears.
Quick referenceVLOOKUP
=VLOOKUP(search_key, range, index, [is_sorted])
=VLOOKUP(F1, A2:C5, 2, FALSE)
=IFERROR(VLOOKUP(F1, A2:C5, 2, FALSE), "Not found")The key ideasSummary: Looking things up with VLOOKUP
VLOOKUP searches the first column of a range for your search key, then returns the value from the column number you ask for. Column 1 is the key column, column 2 is the next one along, and so on. Use FALSE as the last argument for an exact match; without it Sheets assumes the first column is sorted and can return wrong answers.
If the key is not found you get #N/A. Wrapping the lookup in IFERROR lets you show a friendly message instead.
Watch out forCommon mistakes
- Searching a column that is not first.
VLOOKUPonly looks in the left-most column of the range you give it. If your key sits further right, rearrange the range or useINDEXandMATCH. - Forgetting FALSE. Leave out the last argument and Sheets assumes the first column is sorted, which can quietly return the wrong row. For exact lookups always write
FALSE. - Not locking the table. If you fill a lookup down,
A2:C5slides as well. Write it as$A$2:$C$5(see the lesson on absolute references). - Counting columns from the sheet. The index number counts from the first column of your range, not from column A of the sheet.
Next stepTry it in the real Google Sheets
Google Sheets also has XLOOKUP, a newer function that can look in any column, defaults to an exact match and lets you give a value to show when nothing is found. It avoids most of the traps above. VLOOKUP is still everywhere in existing spreadsheets, so it is worth understanding either way. The practice sheet currently supports VLOOKUP only.