☰

VLOOKUP Formula

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

The VLOOKUP function allows you to search for a value in the first column of a range or table and return a value in the same row from another column. It's one of Excel's most powerful lookup functions.

How It Works

VLOOKUP searches vertically (top to bottom) in the first column of your specified range to find your lookup value, then returns a corresponding value from a column you specify to the right.

📋 Parameters Explained

  • lookup_value: The value you want to search for (e.g., "Apple", B2, or 12345)
  • table_array: The range of cells containing your data (e.g., A2:D10)
  • col_index_num: Which column number to return the value from (1 = first column, 2 = second column, etc.)
  • range_lookup: TRUE for approximate match, FALSE for exact match (always use FALSE for most cases)

Example

=VLOOKUP("Apple", A2:C10, 3, FALSE)

This formula searches for "Apple" in the first column of range A2:C10, and returns the value from the 3rd column of the same row. The FALSE ensures an exact match.

Common Use Cases

• Product Lookup: =VLOOKUP(B2, Products!A:D, 2, FALSE)
• Price Lookup: =VLOOKUP("SKU123", A:C, 3, FALSE)
• Employee Data: =VLOOKUP(ID, Staff!A:F, 4, FALSE)

🎯 Practice Task

  • Objective: Use the embedded Excel sheet below to practice the VLOOKUP formula
  • Task: Fill the Product Name (VLOOKUP) column by looking up the Product Code in the lookup table
  • Logic: Find the Product Code in the lookup table and return the corresponding Product Name
  • Formula Hint: =VLOOKUP(A2, LookupTable, 2, FALSE)

Pro Tip: Always use FALSE for the range_lookup parameter unless you specifically need an approximate match. This ensures you get exact matches and avoid unexpected results!

Common VLOOKUP Errors & Solutions

#N/A Error: The lookup value wasn't found. Check for typos or trailing spaces.
#REF! Error: Your column index number is larger than the number of columns in your table.
#VALUE! Error: Your column index number is less than 1.

Next Steps

Master VLOOKUP and then explore XLOOKUP (the modern replacement) and INDEX & MATCH for more flexible lookup solutions. Check out the other lookup functions in the sidebar!