The VLOOKUP function searches for a value in the first column of a table and returns a corresponding value from another column. This guide explains how VLOOKUP works and includes practical examples you can use immediately.
WHAT THE VLOOKUP FUNCTION DOES
VLOOKUP finds a value in the leftmost column of a table and retrieves data from the same row in a different column. The "V" stands for "vertical" lookup, meaning it searches down through rows.
Think of it like looking up someone's phone number in a phone book — you find their name (lookup value) and get their number (return value) from the same row.
WHEN TO USE VLOOKUP
Use VLOOKUP when you want to:
- Retrieve information from a table based on a key value
- Match product codes to descriptions, employee IDs to names, etc.
- Pull data from one sheet or table into another
- Avoid manually searching through large datasets
FORMULA SYNTAX
VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])| Argument | Description |
|---|---|
lookup_value | The value to search for in the first column |
table_array | The range containing your data table |
col_index_num | Which column number to return (1 = first column) |
range_lookup | TRUE for approximate match, FALSE for exact match (optional) |
EXAMPLE 1: LOOK UP EMPLOYEE INFORMATION
We want to find an employee's department based on their ID.
| A | B | C | |
|---|---|---|---|
| 1 | Employee ID | Name | Department |
| 2 | 101 | Alice | Sales |
| 3 | 102 | Bob | Marketing |
| 4 | 103 | Carol | Engineering |
| 5 | 104 | David | Sales |
| 6 | |||
| 7 | Look up ID: | 103 | =VLOOKUP(B7,A2:C5,3,FALSE) |
=VLOOKUP(103, A2:C5, 3, FALSE)Result: "Engineering" (finds 103 in column A, returns value from column 3)
Need help applying this? Try this in Numstro Builder
EXAMPLE 2: PRODUCT PRICE LOOKUP WITH APPROXIMATE MATCH
We want to find pricing tiers based on quantity ordered (price breaks).
| A | B | |
|---|---|---|
| 1 | Min Quantity | Price per Unit |
| 2 | 1 | $10.00 |
| 3 | 50 | $9.00 |
| 4 | 100 | $8.00 |
| 5 | 500 | $7.00 |
| 6 | ||
| 7 | Order quantity: | 75 |
| 8 | Price: | =VLOOKUP(B7,A2:B5,2,TRUE) |
=VLOOKUP(75, A2:B5, 2, TRUE)Result: $9.00 (75 is between 50 and 100, so uses 50-tier pricing)
Need help applying this? Try this in Numstro Builder
COMMON MISTAKES & EDGE CASES
- VLOOKUP only searches to the RIGHT — lookup column must be leftmost in table
- Use FALSE for exact matches (most common), TRUE only for sorted data ranges
- Returns #N/A error if lookup value isn't found with exact match
- Column index starts at 1 (not 0) — first column = 1, second = 2, etc.
- Consider using INDEX/MATCH for more flexible lookups
- Table array should use absolute references ($A$2:$C$5) when copying formulas
RELATED FUNCTIONS
FURTHER READING
Official Microsoft documentation:
VLOOKUP — Microsoft Support