How to use VLOOKUP in Excel
Finds a row by its first column and returns another column.
Syntax
lookup_valueThe value to findtable_arrayThe whole table, first column = where to searchcol_index_numWhich column to return, counting from 1[range_lookup]FALSE for an exact match optionalExample
Here’s VLOOKUP on a small table of orders. The highlighted cells are the ones the formula reads.
=VLOOKUP(1006,A2:F9,3,FALSE)Edit this formula live Change the ranges and watch the answer update. Free, no sign-up.
Common mistakes with VLOOKUP
Forgetting FALSE at the end
Without FALSE (exact match), VLOOKUP assumes your data is sorted and can return a wrong row without any error.
It only looks right
The value you search for must be in the first column of the table. To look left, use XLOOKUP or INDEX/MATCH.
Hard-coded column numbers break
Inserting a column shifts the data but not the 3 in your formula. XLOOKUP avoids this.
What the error messages mean
Type these slips into Gridwise and this is the feedback you get:
Leaving off the closing bracket
=VLOOKUP(1006,A2:F9,3,FALSE
Parentheses don’t match. You have 1 opening and 0 closing parentheses. Every ( needs a ).
Questions
What does the VLOOKUP function do in Excel?
Finds a row by its first column and returns another column. The syntax is VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]).
Why is my VLOOKUP formula giving the wrong answer?
Forgetting FALSE at the end: Without FALSE (exact match), VLOOKUP assumes your data is sorted and can return a wrong row without any error. It only looks right: The value you search for must be in the first column of the table. To look left, use XLOOKUP or INDEX/MATCH. Hard-coded column numbers break: Inserting a column shifts the data but not the 3 in your formula. XLOOKUP avoids this.
Does VLOOKUP work in Google Sheets?
Most Excel functions, including the core ones on this page, work the same way in Google Sheets. A few newer functions arrived in Sheets later or behave slightly differently, so test it on your own data.