AutoExel Logo

VLOOKUP: the ultimate guide

Learn to use VLOOKUP to find data in Excel. Practical examples, common errors and modern alternatives.

VLOOKUP is one of Excel's most powerful formulas. It allows you to search for a value in a table and return related information. It's like having an assistant search the address book for you.

How VLOOKUP works

VLOOKUP 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: the value to search for
  • table_array: the range where to search (e.g., A1:D100)
  • col_index_num: which column to return (1=first, 2=second, etc.)
  • range_lookup: TRUE (approximate) or FALSE (exact) - always use FALSE

Example 1: Find product price

You have a product table with code (col A), name (col B) and price (col C). You want to find the price of a product by entering the code.

Formula:

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

Search for the code in E2 in table A2:C100 and return the value from column 3 (the price).

Step-by-step explanation:

  1. Excel reads the product code in E2
  2. Searches for that code in the first column of the table (column A)
  3. When found, goes to the same row
  4. Returns the value from column 3 (column C with the price)

Example 2: Search customer data

You have a customer list with code, name, email and phone. You want to retrieve the email by entering the customer code.

Formula:

=VLOOKUP(F2, Customers!A:D, 3, FALSE)

Search for the customer code in F2 in the "Customers" sheet, columns A-D, and return the email (column 3).

Example 3: Price list table

Find the price based on quantity ordered, with discounts for higher volumes.

Price list table:

From Quantity | Price
1 | $10.00
50 | $9.50
100 | $9.00
500 | $8.00

Formula:

=VLOOKUP(G2, PriceList!A:B, 2, TRUE)

Note: here we use TRUE because we want approximate match (e.g., 75 pcs = price from 50 pcs)

Common errors and solutions

#N/A (not available)

Problem: The lookup value doesn't exist in the table

Solutions:

  • Check that the value is written exactly the same (watch for extra spaces)
  • Verify that the table includes all necessary rows
  • Use IFERROR to handle the error: =IFERROR(VLOOKUP(...), "Not found")

#REF! (invalid reference)

Problem: The column number is greater than the number of columns in the table

Solution: If the table goes from A to C (3 columns), the maximum number is 3, no more

Wrong value returned

Problem: The formula returns a value but it's not the right one

Solution: You used TRUE instead of FALSE. TRUE searches for approximate match, FALSE searches for exact match. Always use FALSE unless you want approximate values.

Alternatives to VLOOKUP

XLOOKUP (Excel 365 and recent versions)

More powerful and flexible than VLOOKUP. Can search in any column, not just the first.

=XLOOKUP(lookup_value, lookup_array, return_array)

INDEX + MATCH

The most powerful but more complex combination. Works on all Excel versions.

=INDEX(C2:C100, MATCH(E2, A2:A100, 0))

MATCH finds the position, INDEX returns the value

Practical tips

1. Always lock the table with $

Use absolute references to prevent the table from moving when you copy the formula:

=VLOOKUP(E2, $A$2:$C$100, 3, FALSE)

2. Use named ranges

Give your table a name to make formulas more readable:

=VLOOKUP(E2, Products, 3, FALSE)

3. Sort data if using TRUE

If you use approximate match (TRUE), the first column MUST be sorted in ascending order, otherwise the results will be wrong.

No more VLOOKUP complications

With AutoExel you don't need to remember complex syntax. Describe what you want to search and AI creates the correct formula automatically.

Try AutoExel Free

Frequently asked questions

Want to search data without formulas?

AutoExel finds data for you with artificial intelligence. No formulas, no errors, just results.

Start FreeSee Plans