Lookup & reference
How to Use VLOOKUP in Excel (Step-by-Step, with Examples)
VLOOKUP is the function people reach for when they need to find a value in a table and pull back a matching value from another column: looking up a price by product code, an email by name, a salary by employee ID.
It is one of the most-used functions in Excel, and also one of the most error-prone. This guide covers the exact syntax, real worked examples, and how to fix the #N/A and #REF! errors that trip everyone up.
VLOOKUP syntax
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]) | Argument | Required | What it does |
|---|---|---|
lookup_value | Required | The value to search for, in the first column of the table. |
table_array | Required | The range that holds the data. The lookup column must be the leftmost column. |
col_index_num | Required | Which column of the table to return, counting from 1 at the leftmost column. |
range_lookup | Optional | FALSE for an exact match (what you want 99% of the time); TRUE/omitted for an approximate match on sorted data. |
Note: Works the same in Google Sheets, where the fourth argument is called is_sorted.
Examples
Real, copy-paste-ready formulas.
1. Exact match: look up a price by SKU
=VLOOKUP(E2, A2:C100, 3, FALSE) Searches column A for the SKU in E2 and returns the value from the 3rd column (C). FALSE forces an exact match, so a missing SKU returns #N/A instead of the wrong row.
2. Handle "not found" gracefully
=IFERROR(VLOOKUP(E2, A2:C100, 3, FALSE), "Not found") Wrapping VLOOKUP in IFERROR replaces the ugly #N/A with a friendly message, ideal for dashboards and shared sheets.
3. Approximate match: assign a grade band
=VLOOKUP(B2, $E$2:$F$6, 2, TRUE) With TRUE, VLOOKUP finds the largest value less than or equal to the score. The lookup table MUST be sorted ascending for this to work. The $ locks the table so you can fill the formula down.
How to write VLOOKUP step by step
- 1
Click the cell where you want the result and type =VLOOKUP(
- 2
Click the cell holding the value you are searching for (the lookup_value), then type a comma.
- 3
Select the whole table range, starting with the column that contains the lookup value. Press F4 to lock it with $ signs if you will copy the formula.
- 4
Type the column number to return, counting from the left of your table (1, 2, 3…), then a comma.
- 5
Type FALSE for an exact match, then close the bracket ) and press Enter.
Common errors and fixes
| Error | Why it happens | How to fix it |
|---|---|---|
#N/A | The lookup value is not in the first column, often due to extra spaces or a number stored as text. | Use exact match (FALSE), clean values with TRIM(), and make sure both sides are the same data type. Wrap in IFERROR for a friendly fallback. |
#REF! | col_index_num is greater than the number of columns in table_array. | Lower the column number, or widen the table_array so it includes the column you want to return. |
#VALUE! | col_index_num is less than 1, or an argument is malformed. | Make sure the column index is a positive whole number and the arguments are comma-separated correctly. |
Wrong value returned | range_lookup was omitted or TRUE on unsorted data, so VLOOKUP grabbed the nearest match. | Add FALSE as the fourth argument to force an exact match. |
Frequently asked questions
What does FALSE mean in VLOOKUP?
FALSE (or 0) tells VLOOKUP to find an exact match. If the value is not found it returns #N/A. TRUE (or omitting it) does an approximate match, which only works correctly on data sorted in ascending order.
Why is my VLOOKUP returning #N/A?
The most common reasons are: the lookup value is not in the leftmost column, there are trailing spaces, or a number is stored as text on one side. Use exact match, TRIM() to remove spaces, and confirm both values are the same type.
Can VLOOKUP look to the left?
No. VLOOKUP can only return columns to the right of the lookup column. To look left, use XLOOKUP or INDEX/MATCH instead.
Does VLOOKUP work in Google Sheets?
Yes. The syntax is identical; the optional fourth argument is named is_sorted, where FALSE means an exact match.