How to Use VLOOKUP in Excel: Exact Match Examples
By Szabó Gergő · Updated
VLOOKUP searches the first column of a table and returns a value from another column in the same row. It remains useful in older workbooks, but exact matching should be specified explicitly for most business data.
Syntax and arguments
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])- lookup_value
- The value to locate in the first column of the table.
- table_array
- The full lookup table, starting with the search column.
- col_index_num
- The return column number counted from the left edge of the table.
- range_lookup
- Use FALSE for exact match; TRUE performs an approximate match on sorted data.
VLOOKUP examples
Customer IDs are in A and tiers are in C; E2 contains the ID.
=VLOOKUP(E2,$A$2:$C$200,3,FALSE)The fixed table range survives copying, and FALSE requires an exact customer ID.
SKUs are the first column of A2:D100 and price is the fourth.
=VLOOKUP(F2,$A$2:$D$100,4,FALSE)The formula returns column 4 from the row where the SKU exactly matches F2.
Minimum scores sorted ascending are in H2:H6 and grades are in I2:I6.
=VLOOKUP(B2,$H$2:$I$6,2,TRUE)Approximate matching returns the grade for the largest threshold not greater than B2.
Common mistakes
Omitting FALSE for an exact lookup
Add FALSE as the fourth argument unless you intentionally use a sorted threshold table.
Lookup column is not first
Rearrange the table or use XLOOKUP or INDEX/MATCH when the return value is to the left.
Table shifts when copied
Lock the table with absolute references such as $A$2:$D$100.
VLOOKUP FAQ
What does FALSE mean in VLOOKUP?
FALSE tells Excel to require an exact match. It is the safest choice for IDs, names, SKUs, and other discrete values.
Can VLOOKUP look left?
Not directly. Its search column must be the first column in table_array. Use XLOOKUP or INDEX/MATCH for left lookups.
Why does VLOOKUP show #REF!?
The column index is larger than the number of columns in the selected table. Expand the table or reduce col_index_num.