Excel VLOOKUP Function
last modified April 4, 2025
The VLOOKUP function is one of Excel's most powerful lookup and
reference functions. It searches vertically down the first column of a range
for a key and returns a value from a specified column. This tutorial provides
a comprehensive guide to using VLOOKUP with detailed examples.
You'll learn basic syntax, practical applications, and advanced techniques.
VLOOKUP Function Basics
The VLOOKUP function searches for a value in the first column of
a table array and returns a value in the same row from another column. It's
essential for data retrieval tasks in Excel.
| Component | Description |
|---|---|
| Function Name | VLOOKUP |
| Syntax | =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]) |
| Arguments | 4 parameters (last optional) |
| Return Value | Matched value from table array |
This table breaks down the essential components of the VLOOKUP
function. It shows the function name, syntax format, argument details, and
return value characteristics.
Basic VLOOKUP Example
This example demonstrates the simplest use of VLOOKUP to find product prices from a product ID.
| A | B | C |
|---|---|---|
| Product ID | Product Name | Price |
| P100 | Laptop | 999 |
| P101 | Mouse | 25 |
| P102 | Keyboard | 45 |
| =VLOOKUP("P101", A2:C4, 3, FALSE) |
The table shows a product list with IDs, names, and prices. The VLOOKUP formula searches for product ID "P101" and returns its price from column 3.
=VLOOKUP("P101", A2:C4, 3, FALSE)
This formula searches for "P101" in the first column of A2:C4. When found, it returns the value from the 3rd column of the same row. The FALSE parameter ensures exact match. Result will be 25.
VLOOKUP with Cell Reference
A more practical use involves referencing a cell for the lookup value instead of hardcoding it. This makes the formula dynamic.
| A | B | C | D |
|---|---|---|---|
| Product ID | Product Name | Price | Search ID |
| P100 | Laptop | 999 | P102 |
| P101 | Mouse | 25 | |
| P102 | Keyboard | 45 | =VLOOKUP(D2, A2:C4, 3, FALSE) |
This table demonstrates using a cell reference (D2) for the lookup value. The formula in D4 will return the price for whatever product ID is entered in D2.
=VLOOKUP(D2, A2:C4, 3, FALSE)
This formula looks up the value in D2 ("P102") within A2:C4 and returns the price from column 3. The result will be 45. Changing D2 updates the result automatically.
VLOOKUP with Approximate Match
VLOOKUP can perform approximate matches when the last parameter is TRUE or omitted. This is useful for finding category ranges like tax brackets.
| A | B | C |
|---|---|---|
| Income | Tax Rate | Income |
| 0 | 10% | 42000 |
| 20000 | 15% | |
| 40000 | 22% | =VLOOKUP(C2, A2:B4, 2, TRUE) |
The table shows tax brackets with income thresholds and corresponding rates. The VLOOKUP finds the appropriate tax rate for the income in C2 (42000).
=VLOOKUP(C2, A2:B4, 2, TRUE)
This formula finds the largest value in column A that is less than or equal to 42000 (40000) and returns the corresponding tax rate (22%). The table must be sorted in ascending order for this to work correctly.
VLOOKUP with Wildcards
VLOOKUP supports wildcards (* and ?) for partial matches when doing exact lookups (FALSE as last parameter). This helps find values when you know only part of the lookup value.
| A | B | C |
|---|---|---|
| Product Name | Price | Search Term |
| Wireless Mouse | 25 | *Mouse |
| Bluetooth Keyboard | 45 | |
| USB Cable | 10 | =VLOOKUP(C2, A2:B4, 2, FALSE) |
The table demonstrates using wildcards in VLOOKUP. The formula searches for any product ending with "Mouse" and returns its price.
=VLOOKUP(C2, A2:B4, 2, FALSE)
This formula searches for any value in column A that ends with "Mouse" (as specified by "*Mouse" in C2) and returns the corresponding price. The asterisk matches any sequence of characters. Result will be 25.
VLOOKUP with Multiple Criteria
While VLOOKUP normally handles single criteria, you can combine it with helper columns to handle multiple criteria lookups.
| A | B | C | D | E |
|---|---|---|---|---|
| Region | Product | Combined | Sales | Search |
| East | Widget | EastWidget | 1500 | WestGadget |
| West | Gadget | WestGadget | 2200 | |
| North | Tool | NorthTool | 1800 | =VLOOKUP(E2, C2:D4, 2, FALSE) |
The table shows sales data by region and product. Column C combines region and product to create a unique lookup key. The formula finds sales for West Gadget.
=VLOOKUP(E2, C2:D4, 2, FALSE)
This formula searches for "WestGadget" in the combined key column (C) and returns the corresponding sales from column D. The result will be 2200. Helper columns enable multi-criteria lookups with VLOOKUP.
VLOOKUP Common Errors
VLOOKUP can return several error values when things go wrong. Understanding these helps troubleshoot formulas.
| Error | Cause | Solution |
|---|---|---|
| #N/A | Lookup value not found | Check spelling or use IFERROR |
| #REF! | Column index out of range | Adjust col_index_num |
| #VALUE! | Invalid arguments | Check parameter types |
This table lists common VLOOKUP errors, their causes, and potential solutions. #N/A is the most frequent, occurring when the lookup value isn't found.
VLOOKUP Limitations
While powerful, VLOOKUP has some important limitations to be aware of when designing your spreadsheets.
| Limitation | Description |
|---|---|
| Left lookup | Cannot look to the left of key column |
| Single criteria | Natively handles only one lookup value |
| First match | Returns only the first matching value |
| Static column | Column index doesn't adjust automatically |
The table outlines key VLOOKUP limitations. These constraints sometimes make INDEX/MATCH a better alternative for complex lookup scenarios.
The VLOOKUP function is indispensable for Excel users who need to
retrieve data from tables. From simple exact matches to approximate range
lookups and wildcard searches, VLOOKUP handles many common data tasks.
Understanding its parameters and limitations will help you use it effectively.
For more complex scenarios, consider learning INDEX/MATCH as an alternative.
Author
List all Excel Formulas.