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.
Ankit Srivastava is an IT trainer, technology educator, and digital skills mentor with expertise in programming, data analytics, AI, and software development. He has successfully trained thousands of learners, with more than 10,000 student enrollments on Udemy. His practical teaching approach empowers students and professionals to build in-demand technical skills. Colorstech channel where Ankit posts video tutorials has more than 8000 Subscribers.
