Learn how to use Excel’s VLOOKUP function effectively. VLOOKUP (Vertical Lookup) searches for a value in the first column of a table and returns a value from the same row in another column.

Syntax

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

• lookup_value: What you want to find (number, text, or cell reference)
• table_array: Where to look for the value (must include lookup column and return column)
• col_index_num: Which column contains the return value (1 is leftmost column)
• range_lookup: (Optional) TRUE for approximate match, FALSE for exact match. Default is TRUE

Examples

1. Find product price by ID (exact match):
   =VLOOKUP(“A123”, Products!A2:C100, 3, FALSE)

   • Looks for “A123” in first column
   • Returns value from third column
   • FALSE ensures exact match

2. Look up employee name by ID:
   =VLOOKUP(D2, EmployeeList!A:C, 2, FALSE)

   • Uses cell reference D2 as lookup value
   • Searches in EmployeeList table
   • Returns name from second column

3. Find closest value in sorted list:
   =VLOOKUP(25, PriceList!A:B, 2, TRUE)

   • Finds closest value to 25
   • TRUE allows approximate match
   • List must be sorted ascending

4. Multiple VLOOKUP in one formula:
   =VLOOKUP(A2, Data!A:D, {2,3,4}, FALSE)

   • Returns values from columns 2, 3, and 4
   • Creates array of results

Advanced Tips

• Table preparation:

  • Lookup column must be leftmost
  • Sort data for approximate matches
  • No blank rows in table
  • Unique lookup values for exact matches

• Error handling:

  • #N/A: Value not found
  • #REF!: Column index too large
  • #VALUE!: Invalid data type
  • IFERROR for custom messages

• Performance optimization:

  • Use exact match when possible
  • Limit table range size
  • Avoid volatile references

Common Mistakes

• Wrong column index (starts at 1, not 0)
• Unsorted data with approximate match
• Missing FALSE for text lookups
• Lookup column not leftmost
• Range_lookup omitted (defaults to TRUE)