The VLOOKUP function is used to search for a value in the first column of a range and return a value in the same row from another column in the range.
Syntax:
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
- lookup_value: The value to search for.
- table_array: The range of cells that contains the data.
- col_index_num: The column number (in the range) from which to return the value.
- range_lookup: Optional; TRUE for approximate match or FALSE for exact match.
Example: Employee Data Lookup
Sample Data:
| ID | Name | Department | Salary |
|---|---|---|---|
| 101 | John Smith | IT | 60000 |
| 102 | Mary Johnson | HR | 55000 |
| 103 | Alex Brown | Finance | 75000 |
| 104 | Chris Lee | Marketing | 52000 |
| 105 | Sarah Wilson | IT | 58000 |
Task:
Find the Department and Salary of an employee with ID 103.
Step 1: Use VLOOKUP for Department
- Formula:
=VLOOKUP(103, A2:D6, 3, FALSE) - Explanation:
- 103: The value to look for in the first column.
- A2:D6: The table array containing the data.
- 3: The column number for the Department.
- FALSE: An exact match is required.
- Result: Finance
Step 2: Use VLOOKUP for Salary
- Formula:
=VLOOKUP(103, A2:D6, 4, FALSE) - Explanation:
- 103: The value to look for in the first column.
- A2:D6: The table array containing the data.
- 4: The column number for the Salary.
- FALSE: An exact match is required.
- Result: 75000
Key Notes:
- The lookup_value (e.g., ID) must be in the first column of the table.
- The col_index_num specifies the column to return a value from (relative to the table range).
- Use FALSE for an exact match (recommended for most use cases).
Use Case Example:
| ID | Lookup Department Formula | Lookup Salary Formula |
|---|---|---|
| 103 | =VLOOKUP(103, A2:D6, 3, FALSE) | =VLOOKUP(103, A2:D6, 4, FALSE) |
Additional Examples with VLOOKUP
Example 1: Looking Up Employee Name by ID
Suppose you have a dropdown in cell F1 where users can enter an ID, and you want to display the corresponding Name in G1.
Formula in G1: =VLOOKUP(F1, A2:D6, 2, FALSE)
- If F1 contains 105, result: Sarah Wilson
- This dynamic setup allows quick lookups without rewriting the formula each time.
Example 2: Handling Errors with IFERROR
VLOOKUP returns #N/A if the lookup_value isn’t found. To make your sheet cleaner:
Formula: =IFERROR(VLOOKUP(F1, A2:D6, 3, FALSE), "Employee Not Found")
- If ID 999 is entered (not in the table), it displays “Employee Not Found” instead of an error.
Example 3: Approximate Match with TRUE
Assume the first column (ID) is sorted ascending. You can use approximate match for range-based lookups.
Imagine a commission table:
| Sales Amount | Commission Rate |
|---|---|
| 0 | 0% |
| 50000 | 5% |
| 100000 | 10% |
Formula to find rate for $72,000 sales: =VLOOKUP(72000, H2:I4, 2, TRUE)
Result: 5% (since 72000 falls between 50000 and 100000).
Note: Always sort the first column ascending for TRUE to work correctly.
Example 4: VLOOKUP with Wildcards for Partial Matches
Use wildcards when range_lookup is FALSE for text searches.
Suppose you want to find employees with “Smith” in their name (but name is not in first column—wait, VLOOKUP can’t search non-first columns directly).
Actually, for partial text matches in the lookup column:
Formula: =VLOOKUP("*Smith*", A2:D6, 2, FALSE)
This would return the first matching name containing “Smith” (John Smith).
Example 5: Combining VLOOKUP with Other Functions
Calculate bonus as 10% of salary for a given ID.
Formula: =VLOOKUP(F1, A2:D6, 4, FALSE) * 0.1
If F1 is 103, result: 7500 (10% of 75000).
Or display full details: =VLOOKUP(F1, A2:D6, 2, FALSE) & " works in " & VLOOKUP(F1, A2:D6, 3, FALSE) & " earning $" & VLOOKUP(F1, A2:D6, 4, FALSE)
Result for 103: Alex Brown works in Finance earning $75000
Common VLOOKUP Pitfalls and How to Avoid Them
- #N/A Error: Lookup value not found. Use IFERROR or check data/types (e.g., ID as text vs number).
- #REF! Error: col_index_num larger than columns in table_array.
- Wrong Results: Forgetting FALSE leads to unexpected approximate matches.
- Data Type Mismatch: Ensure lookup_value and first column are same type (use TEXT or VALUE functions if needed).
- Table Array Not Locked: When copying formulas, use absolute references:
$A$2:$D$6.
When to Use Alternatives
While VLOOKUP is great for vertical lookups from left to right, consider:
- XLOOKUP (Excel 365/2021+): More flexible, can search right-to-left, return arrays, better error handling.
- INDEX + MATCH: Powerful combo for lookups in any direction.
- HLOOKUP: For horizontal data.
Mastering VLOOKUP builds a strong foundation for efficient data management in Excel. Practice with larger datasets, combine with tables (Ctrl+T), and explore dynamic named ranges for even better results.
Product Inventory Lookup Example
Let’s explore VLOOKUP with a different dataset: a product inventory table for an online store.
Assume the data is in cells A1:E11 (with headers in row 1):
| Product ID | Product Name | Category | Price | Stock |
|---|---|---|---|---|
| P101 | Wireless Mouse | Electronics | 25.99 | 150 |
| P102 | Mechanical Keyboard | Electronics | 89.99 | 80 |
| P103 | USB-C Hub | Accessories | 45.50 | 200 |
| P104 | Laptop Stand | Accessories | 34.99 | 120 |
| P105 | Noise-Cancelling Headphones | Electronics | 199.99 | 50 |
| P106 | External SSD 1TB | Storage | 119.99 | 95 |
| P107 | Webcam HD | Electronics | 69.99 | 110 |
| P108 | Ergonomic Chair | Furniture | 299.99 | 30 |
| P109 | Monitor 27″ | Electronics | 249.99 | 65 |
| P110 | Cable Organizer | Accessories | 12.99 | 300 |
The table_array for all examples below is A2:E11.
Example 1: Lookup Product Name and Price for a Given ID
Enter Product ID in cell G1 (e.g., P106).
- Name formula in H1:
=VLOOKUP(G1, $A$2:$E$11, 2, FALSE) - Result for P106: External SSD 1TB
- Price formula in I1:
=VLOOKUP(G1, $A$2:$E$11, 4, FALSE) - Result for P106: 119.99
Example 2: Find Category and Stock Level
For Product ID P108:
- Category:
=VLOOKUP("P108", $A$2:$E$11, 3, FALSE) - Result: Furniture
- Stock:
=VLOOKUP("P108", $A$2:$E$11, 5, FALSE) - Result: 30
This helps quickly check availability for high-value items like chairs.
Example 3: Calculate Total Value of Remaining Stock
Combine VLOOKUP with multiplication for inventory valuation.
Formula: =VLOOKUP(G1, $A$2:$E$11, 4, FALSE) * VLOOKUP(G1, $A$2:$E$11, 5, FALSE)
- If G1 is P105 (Noise-Cancelling Headphones):
- Price: 199.99 × Stock: 50 = 9999.5
- Result: 9999.5 (total value in dollars)
Add currency formatting for professional reports.
Example 4: Low Stock Alert Using IF and VLOOKUP
Flag products with low stock (e.g., below 60 units).
Formula: =IF(VLOOKUP(G1, $A$2:$E$11, 5, FALSE) < 60, "Low Stock - Reorder!", "In Stock")
- For P105 (stock 50): Low Stock – Reorder!
- For P103 (stock 200): In Stock
Great for dashboard alerts.
Example 5: Display Full Product Details in One Cell
Concatenate multiple VLOOKUPs for a summary sentence.
Formula: =VLOOKUP(G1, $A$2:$E$11, 2, FALSE) & " is in the " & VLOOKUP(G1, $A$2:$E$11, 3, FALSE) & " category, priced at $" & VLOOKUP(G1, $A$2:$E$11, 4, FALSE) & " with " & VLOOKUP(G1, $A$2:$E$11, 5, FALSE) & " units in stock."
- For P109: Monitor 27″ is in the Electronics category, priced at $249.99 with 65 units in stock.
This creates readable product descriptions for reports or customer inquiries.
These examples show how versatile VLOOKUP becomes with real-world inventory data—perfect for pricing checks, stock management, and quick reporting.
