Practical guide

How to Use VLOOKUP in Excel

VLOOKUP searches down the first column of a table and returns a value from a column to its right. The final argument controls whether a match must be exact.

What to do

  1. Use the pattern =VLOOKUP(E2,A2:C100,3,FALSE) to find E2 in the first column of A2:C100 and return the third column.
  2. Use FALSE for an exact match in most ID, product-code, and name lookups.
  3. Lock a lookup range before filling the formula down, for example $A$2:$C$100.
  4. If the result is #N/A, check for extra spaces, different data types, or a lookup key absent from the first column.

Checks and limitations

  • VLOOKUP cannot return a value to the left of its lookup column; XLOOKUP or INDEX and MATCH can handle more layouts.
  • Omitting the final match argument can allow approximate matching, which is unsafe when the lookup column is not sorted as expected.

What VLOOKUP does

Use VLOOKUP with an exact match to find a key in the first column and return a corresponding value.

Syntax

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

Copyable formula

=VLOOKUP(E2,A2:C5,3,FALSE)

Example result: 120 for Widget.

Example data

ItemRegionSalesStatus
WidgetNorth120Paid
CableSouth80Paid
WidgetNorth200Pending
DeskNorth150Paid

Real-world use

Look up a product price from a reference table using a product name or ID.

Common mistakes

  • Omitting FALSE for an exact match when the lookup column is not sorted for approximate matching.
  • Changing the return-column index when inserting columns into the lookup table.

Alternative formulas

XLOOKUP is more flexible in newer Excel. INDEX with MATCH supports older layouts and leftward lookups.

Compatibility

VLOOKUP is widely supported. Use FALSE for an exact match; older Excel may not include XLOOKUP.

Frequently asked questions

Can VLOOKUP search to the left?

No. Its lookup column must be the first column in the selected table array.

Why does VLOOKUP return #N/A?

Check whether the key exists and whether text, numbers, spaces, and data types match.

Browse the Excel formula library ยท Open the percentage calculator