Need to find information in a large table? VLOOKUP searches for a value in the first column and returns data from any column to the right. It’s one of Excel’s most-used functions for a reason.
To download the exercise file for this function, click here.
What Makes VLOOKUP Useful
VLOOKUP saves you from scrolling through endless rows:
- Quick lookups – Find data in seconds instead of minutes
- Automatic updates – Results change when your source data changes
- Works with large datasets – Handle thousands of rows without slowing down
- Approximate matching – Find the closest match for price tiers or ranges
- Cross-sheet lookups – Pull data from other worksheets in your workbook
The Syntax
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])The lookup_value is what you’re searching for. Table_array is where you’re searching. Col_index_num tells Excel which column to return.
Example 1: Basic Product Lookup
Find the price for product P002:
| Product Code | Product Name | Price | Stock |
|---|---|---|---|
| P001 | Wireless Mouse | $29.99 | 45 |
| P002 | USB Keyboard | $45.50 | 23 |
| P003 | Monitor Stand | $67.25 | 12 |
Formula: =VLOOKUP("P002", A2:D4, 3, FALSE)
Result: $45.50
Excel searches column A for “P002”, then returns the value from column 3 (the Price column). FALSE means exact match only.
Example 2: Using Cell References
Look up the product code entered in cell F1:
| Product Code | Product Name | Price |
|---|---|---|
| P001 | Wireless Mouse | $29.99 |
| P002 | USB Keyboard | $45.50 |
| P003 | Monitor Stand | $67.25 |
Formula: =VLOOKUP(F1, A2:C4, 2, FALSE)
Result: Returns the product name for whatever code is in F1
This makes your lookup dynamic. Change F1 and the result updates automatically.
Example 3: Commission Tiers with Approximate Match
Find the commission rate for $32,000 in sales:
| Sales Threshold | Commission Rate |
|---|---|
| $0 | 5% |
| $10,000 | 7% |
| $25,000 | 10% |
| $50,000 | 12% |
Formula: =VLOOKUP(32000, A2:B5, 2, TRUE)
Result: 10%
TRUE tells Excel to find the closest match without going over. The first column must be sorted ascending for this to work.
Example 4: Looking Up from Another Sheet
Pull employee data from a sheet named “ProductList”:
Formula: =VLOOKUP("P002", ProductList!A:D, 3, FALSE)
Result: Returns data from column 3 of the ProductList sheet
Use SheetName! before the range. This keeps your data organized across multiple sheets.
Example 5: Handling Errors with IFERROR
What if the lookup value doesn’t exist?
| Product Code | Product Name | Price |
|---|---|---|
| P001 | Wireless Mouse | $29.99 |
| P002 | USB Keyboard | $45.50 |
Formula: =IFERROR(VLOOKUP("P999", A2:C3, 3, FALSE), "Not found")
Result: “Not found”
IFERROR wraps your VLOOKUP and shows a custom message instead of #N/A errors.
Example 6: Tax Bracket Calculation
Find the tax rate for income of $75,000:
| Income Level | Tax Rate |
|---|---|
| $0 | 10% |
| $10,000 | 12% |
| $40,000 | 22% |
| $85,000 | 24% |
Formula: =VLOOKUP(75000, A2:B5, 2, TRUE)
Result: 22%
Approximate match finds $40,000 (the highest value that doesn’t exceed $75,000) and returns its tax rate.
Range Lookup Options
| Value | Behavior | When to Use |
|---|---|---|
| FALSE or 0 | Exact match only | Product codes, employee IDs, specific items |
| TRUE or 1 | Approximate match | Price tiers, tax brackets, grade ranges |
Common Column Index Numbers
If your table has 5 columns and you want:
- Column 1: Use 1
- Column 2: Use 2
- Column 3: Use 3
- And so on…
Count from the first column of your table_array, not from column A of the spreadsheet.
Important Notes
- VLOOKUP only searches the first column of your range
- It can’t look to the left (only returns columns to the right)
- The first column must be sorted ascending when using TRUE
- Column index must be a positive number
- Table_array must include both the lookup column and return column
Start Using It
Pick a table in your spreadsheet. Choose something to look up. Write a VLOOKUP formula. Try it with FALSE first, then experiment with TRUE for approximate matches.
VLOOKUP becomes second nature after a few tries.
Questions about VLOOKUP? Need help with a specific lookup scenario? Let’s connect.