The VLOOKUP function in Excel is a search tool. It looks for a value in the first column of a table and returns a related value from another column in the same row. This is especially useful for looking up data in large lists or tables.
What is the VLOOKUP Function?
VLOOKUP stands for Vertical Lookup. It searches vertically down a column until it finds a match, then returns the value from a column you choose.
VLOOKUP is often used for tasks like finding prices, retrieving product details, or matching IDs with names.
Syntax of the VLOOKUP Function:
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
- lookup_value: The value you want to find in the first column.
- table_array: The range of cells containing the data (must include the column you search in and the column you return from).
- col_index_num: The column number (in the table_array) from which to return the value.
- [range_lookup]: TRUE (approximate match) or FALSE (exact match).
Where Can We Use the VLOOKUP Function?
The VLOOKUP function is helpful in scenarios such as:
- Finding Prices or Rates: Look up a price from a product list.
- Matching IDs to Names: Quickly match employee IDs to their names.
- Merging Data: Combine information from two tables based on a common value.
- Finding Grades: Return a grade from a score table.
- Inventory Tracking: Find stock quantity for a specific item.
How to Use the VLOOKUP Function in Different Methods
Below are four common methods to use VLOOKUP in Excel:
Method 1: Exact Match Lookup
This method finds an exact match for your lookup value.
Steps:
- Enter your data. For example:
| A (Product ID) | B (Product Name) | C (Price) |
|---|---|---|
| 101 | Apple | 1.5 |
| 102 | Banana | 0.8 |
| 103 | Orange | 1.2 |
- In cell E1, type your lookup value (e.g.,
102). - In cell F1, type:
=VLOOKUP(E1, A2:C4, 3, FALSE) - Press Enter. The result (0.8) will appear.
Live Example:
| Lookup Value | Formula | Result |
|---|---|---|
| 102 | =VLOOKUP(E1, A2:C4, 3, FALSE) | 0.8 |
Method 2: Approximate Match Lookup
When the lookup value is not an exact match, VLOOKUP can find the closest lower value (used for ranges like grades or tax rates).
Steps:
- Enter your data. For example:
| A (Score) | B (Grade) |
|---|---|
| 0 | F |
| 50 | D |
| 60 | C |
| 70 | B |
| 85 | A |
- In cell D1, type a score (e.g.,
68). - In cell E1, type:
=VLOOKUP(D1, A2:B6, 2, TRUE) - Press Enter. The result (C) will appear.
Method 3: Lookup Using a Cell Reference in Formula
You can make your lookup value dynamic by referencing another cell.
Steps:
- Enter your product list in A2:C4 (same as in Method 1).
- In E1, type the Product ID you want (e.g.,
103). - In F1, type:
=VLOOKUP(E1, A2:C4, 2, FALSE) - Press Enter. The result (Orange) will appear.
Method 4: Handling Errors with IFERROR
If the value is not found, VLOOKUP will return #N/A. You can use IFERROR to display a friendly message instead.
Steps:
- Use the same table from Method 1.
- In E1, type a Product ID that doesn’t exist (e.g.,
999). - In F1, type:
=IFERROR(VLOOKUP(E1, A2:C4, 3, FALSE), "Not Found") - Press Enter. The result (“Not Found”) will appear.
Live Example Showing Progress in Each Step
Data Setup:
| A (ID) | B (Name) | C (Price) |
|---|---|---|
| 101 | Apple | 1.5 |
| 102 | Banana | 0.8 |
| 103 | Orange | 1.2 |
Goal:
Look up prices or names based on Product IDs.
Step 1: Enter the table above in A1:C4.
Step 2: Enter lookup values in Column E.
Step 3: Apply VLOOKUP formulas in Column F.
Final Table:
| Lookup Value | Result | Formula |
|---|---|---|
| 102 | 0.8 | =VLOOKUP(E2, A2:C4, 3, FALSE) |
| 103 | Orange | =VLOOKUP(E3, A2:C4, 2, FALSE) |
| 999 | Not Found | =IFERROR(VLOOKUP(E4, A2:C4, 3, FALSE), “Not Found”) |
Key Notes About the VLOOKUP Function
- Searches Vertically: Always searches in the first column of the range.
- Column Number: The column number in the table_array starts at 1 (leftmost column).
- Exact vs Approximate: Use FALSE for exact matches and TRUE for approximate matches.
- Cannot Look Left: VLOOKUP only returns values to the right of the first column.
- Error Handling: Use IFERROR to make results more user-friendly.
Conclusion
The VLOOKUP function in Excel is a powerful way to search for information in large datasets. From finding prices to matching IDs and retrieving related data, VLOOKUP makes looking up values quick and accurate.
By mastering both exact and approximate matches, and using error handling, you can make your lookups more reliable and user-friendly. Practice with real datasets to become confident with VLOOKUP and improve your Excel skills.
Leave a Reply