Learning how to use VLOOKUP in Excel is essential for anyone dealing with spreadsheets and large data sets. VLOOKUP, short for “Vertical Lookup,” allows you to search for a value in the first column of a table and return a corresponding value in the same row from another column. This guide will walk you through the step-by-step process of using VLOOKUP in Excel, with real-world examples, tips, and advanced techniques.
Whether you’re an Excel beginner or looking to sharpen your data analysis skills, this guide will help you master the VLOOKUP formula in Excel effectively.
ALSO READ: How to Remove Duplicates in Excel? 2 Quick and Simple Ways
What is VLOOKUP in Excel?
VLOOKUP (Vertical Lookup) is an Excel function used to find specific data in a vertical column of a table. It’s widely used in data analysis, financial modeling, and reporting.
Syntax of the VLOOKUP Function
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
Explanation:
- lookup_value – The value you want to search for.
- table_array – The range of cells that contains the data.
- col_index_num—The column number in the table from which to retrieve the value.
- range_lookup—Optional. Use FALSE for an exact match, TRUE for an approximate match.
Step-by-Step Guide: How to Use VLOOKUP in Excel
Step 1: Prepare Your Data
Ensure your data is organized with the lookup column (the one you’re searching in) as the first column in your table array.
Example Dataset:
| Product ID | Product Name | Price |
|---|---|---|
| 101 | Keyboard | $25 |
| 102 | Mouse | $15 |
| 103 | Monitor | $200 |
Step 2: Enter the VLOOKUP Formula
Let’s say you want to find the price of the product with ID 102.
Place the following formula in a cell:
=VLOOKUP(102, A2:C4, 3, FALSE)
This tells Excel:
- Look for 102 in the first column of the range A2:C4
- Return the value from the 3rd column (Price)
- Use FALSE to get an exact match
Result:
$15
ALSO READ: What is the excel formula for subtracting
VLOOKUP Example with Cell Reference
Instead of typing the lookup value manually, you can use a cell reference.
=VLOOKUP(A4, A2:C4, 3, FALSE)
If E2 contains 103, the formula returns $200.
Using VLOOKUP for Approximate Match
VLOOKUP can also return approximate matches, useful for things like grading or commission slabs.
Example:
| Score | Grade |
|---|---|
| 0 | F |
| 60 | D |
| 70 | C |
| 80 | B |
| 90 | A |
Formula:
=VLOOKUP(75, A2:B6, 2, TRUE)
This returns C because 75 is closest to 70 without exceeding it.
Common Errors with VLOOKUP and How to Fix Them
1. #N/A Error
- Occurs when the value isn’t found.
- Fix: Check for typos or use IFERROR:
=IFERROR(VLOOKUP(102, A2:C4, 3, FALSE), "Not Found")
2. #REF! Error
- Column index is out of range.
- Fix: Ensure your column index is valid within your table range.
Tips to Use VLOOKUP Effectively
- Always lock your table_array using absolute references (
$A$2:$C$4) when copying formulas. - VLOOKUP only searches left to right—it can’t look left in the table.
- Use named ranges for clarity and easier maintenance.
- For left-lookup functionality, use INDEX and MATCH as an alternative.
Advanced: Combine VLOOKUP with Other Excel Functions
Example: VLOOKUP with IF
=IF(VLOOKUP(E2, A2:C4, 3, FALSE) > 100, "Expensive", "Affordable")This returns “Expensive” if the product price is greater than 100.
When to Use VLOOKUP in Excel
Use VLOOKUP when:
- You have a large dataset and need to find matching values.
- You’re working with structured tables.
- You need a simple, quick solution for vertical lookup tasks.
VLOOKUP vs. XLOOKUP: What’s the Difference?
| Feature | VLOOKUP | XLOOKUP |
|---|---|---|
| Search Range | Vertical only | Vertical & Horizontal |
| Lookup Direction | Left to Right only | Any direction |
| Default Match | Approximate | Exact |
Note: XLOOKUP is available in Excel 365 and Excel 2019+.
Final Thoughts
Learning how to use VLOOKUP in Excel opens up powerful data management capabilities. From simple lookups to combining functions for more advanced operations, VLOOKUP is a must-have in your Excel toolkit.
Frequently Asked Questions (FAQs)
Q1: Can VLOOKUP search from right to left?
No. Use INDEX + MATCH or XLOOKUP instead.
Q2: Is VLOOKUP case-sensitive?
No. Use INDEX + MATCH + EXACT for case-sensitive lookups.
Q3: What is the limit of VLOOKUP in Excel?
It can search up to 255 columns but is limited to vertical lookup.
