How to Use VLOOKUP in Excel
VLOOKUP finds a value in a table by searching the first column and returning a result from another column. Here's exactly how it works — and when to use XLOOKUP instead.
VLOOKUP is one of the most-used formulas in Excel. It searches for a value in the leftmost column of a range, then returns a value from a column you specify to the right. If you've ever needed to pull data from one table into another, VLOOKUP is usually the answer.
The syntax
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])- lookup_value — The value you're searching for (e.g., a product ID, employee name, or order number)
- table_array — The range that contains your data, starting with the column you're searching
- col_index_num — Which column in that range to return (1 = first column, 2 = second, etc.)
- range_lookup — Use
FALSEfor an exact match (almost always what you want)
Step-by-step example
Say you have a product table in columns A–C: Product ID, Product Name, Price. You want to pull the price for a specific product ID into another sheet.
Step 1. Click the cell where you want the price to appear.
Step 2. Type the formula, referencing the product ID you want to look up (say it's in cell E2):
=VLOOKUP(E2, A:C, 3, FALSE)This searches column A for the value in E2, then returns whatever is in the 3rd column (Price).
Step 3. Press Enter. Drag the formula down to fill other rows.
✦ TIP
Lock the table reference with dollar signs so it doesn't shift when you drag: =VLOOKUP(E2, $A:$C, 3, FALSE)
Common errors and fixes
| Error | Cause | Fix |
|---|---|---|
#N/A | Lookup value not found in the first column | Check for extra spaces; make sure data types match (text vs. number) |
#REF! | col_index_num is larger than the number of columns in your range | Reduce the index number or expand your range |
| Wrong result | range_lookup not set to FALSE | Add FALSE as the fourth argument |
⚠ WATCH OUT
VLOOKUP can only look to the right. If your lookup column isn't the leftmost column in your range, use XLOOKUP or INDEX/MATCH instead.
When to use XLOOKUP instead
If you're on Microsoft 365 or Excel 2021+, XLOOKUP is almost always better:
=XLOOKUP(E2, A:A, C:C, "Not found")XLOOKUP can look left or right, handles missing values gracefully with a default, and doesn't break when you insert columns. The only reason to still use VLOOKUP is compatibility with older Excel versions.
The Griddy way
If you're not sure which lookup formula to use, or you keep getting #N/A errors and can't figure out why — just describe what you're trying to do in plain English. Griddy writes the correct formula for your exact data, runs it, and explains what it did.
"Pull the price from the Products sheet for each order ID in column A"
That's it. No formula syntax required.
Skip the manual work
Describe it. Griddy does it.
Instead of writing this formula yourself, just tell Griddy what you need in plain English. Works in Excel and Google Sheets.