Excel + Google Sheets · Deep guide
VLOOKUP in Excel: find a value in a table
Looking up a price, a name or a code in a long table by eye is slow and error prone. VLOOKUP finds the row that matches a key and returns a value from another column of the same row, so your sheet answers the question for you.
VLOOKUP works in every modern version of Excel and in Google Sheets. This guide explains the four arguments, walks through an example with real numbers and shows how to avoid the errors that appear when the key is missing or duplicated. See also: COUNTIF guide and SUMIF guide.
How VLOOKUP works
VLOOKUP takes four arguments: the value to find, the table, the column number to return and whether the match is exact. Write the formula like this:
=VLOOKUP("c",B2:C6,2,FALSE)Excel looks for the letter c in the first column of the table and returns the value from column 2 of that row. Always finish with FALSE so that only an exact match is accepted. Works in Excel 2010 or later.
Step by step example
- Type the keys in column B and the values in column C.
- Click an empty cell, for example E2.
- Type
=VLOOKUP("c",B2:C6,2,FALSE)and press Enter. - Compare the result with the table below.
| A | B | |
|---|---|---|
| 1 | Category (B) | Price (C) |
| 2 | a | 10 |
| 3 | b | 20 |
| 4 | c | 30 |
| 5 | b | 40 |
| 6 | d | 50 |
The expected result is 30, the value that sits next to c. If the key appears twice, VLOOKUP returns the first match, so check for duplicates before you trust the result.
Common errors
- The key is missing and Excel shows the not available error. Wrap the formula in IFERROR to show a friendly message.
- You forgot FALSE and the lookup uses approximate matching, which gives wrong values on unsorted data.
- The key column is not the first column of the table. VLOOKUP only searches the first column.
- Numbers stored as text do not match real numbers. Convert both sides to the same type.
If you use Excel 365 you may prefer XLOOKUP, which searches in any direction and has a built in value for missing keys. VLOOKUP remains the safest choice when you share files with people on older versions.