The VLOOKUP formula combined with the MATCH function is a powerful way to perform dynamic lookups in Excel. By using MATCH within VLOOKUP, you can create lookups where the column index is not hard-coded but dynamically determined based on certain criteria.
1. VLOOKUP Overview:
- Purpose: Searches for a value in the first column of a range and returns a value from a specified column in the same row.
- Syntax:
VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])- lookup_value: The value to search for.
- table_array: The range where the search happens.
- col_index_num: The column number from which to return a value.
- range_lookup: TRUE for approximate match, FALSE for exact match.
2. MATCH Overview:
- Purpose: Finds the position of a value within a row or column.
- Syntax:
MATCH(lookup_value, lookup_array, [match_type])- lookup_value: The value to search for.
- lookup_array: The range to search within.
- match_type: 1 for largest value ≤ lookup_value, 0 for exact match, -1 for smallest value ≥ lookup_value.
3. Why Combine VLOOKUP and MATCH?
Normally, the VLOOKUP function requires a static column index number (col_index_num), which makes it less flexible. By using the MATCH function, we can dynamically determine the column number based on a lookup value. This makes the lookup more robust, especially when dealing with changing data structures.
4. Example 1: VLOOKUP with MATCH
Scenario:
You have a table with names in the first column and different months (like Jan, Feb, Mar) in the following columns. You want to find the sales of “John” for “Feb”.
| Name | Jan | Feb | Mar |
|---|---|---|---|
| John | 100 | 150 | 200 |
| Alice | 120 | 130 | 140 |
| Mark | 110 | 170 | 180 |
Formula:
=VLOOKUP("John", A1:D4, MATCH("Feb", A1:D1, 0), FALSE)Explanation:
VLOOKUP("John", A1:D4, ...): This searches for “John” in the first column (A1:A4).MATCH("Feb", A1:D1, 0): This finds the position of “Feb” in the header row (A1:D1). It returns 2 (since “Feb” is the second column).- VLOOKUP uses the result from MATCH: It retrieves the value from the Feb column in the same row as “John”.
Result: 150
5. Example 2: VLOOKUP with MATCH (Product Prices)
Scenario:
You have a product catalog with Product Names in the first column and prices for different regions (East, West, North) in other columns. You want to find the price of “Laptop” in the “West” region.
| Product | East | West | North |
|---|---|---|---|
| Laptop | 500 | 550 | 520 |
| Phone | 300 | 320 | 310 |
| Tablet | 200 | 210 | 220 |
Formula:
=VLOOKUP("Laptop", A1:D4, MATCH("West", A1:D1, 0), FALSE)Explanation:
VLOOKUP("Laptop", A1:D4, ...): This searches for “Laptop” in the first column (A1:A4).MATCH("West", A1:D1, 0): This finds the position of “West” in the header row (A1:D1). It returns 3 (since “West” is the third column).- VLOOKUP uses the result from MATCH: It retrieves the value from the West column in the same row as “Laptop”.
Result: 550
6. Key Benefits of Using MATCH with VLOOKUP:
- Dynamic column index: The formula adapts if columns are added, removed, or reordered.
- Ease of maintenance: You don’t need to manually update the column index in the VLOOKUP formula.
Join Our WhatsApp Group
Nazim Khan (Author) 📞 +91 9536250020
[MBA in Finance]
Nazim Khan is an expert in Microsoft Excel. He teaches people how to use it better. He has been doing this for more than ten years. He is running this website (TechGuruPlus.com) and a YouTube channel called "Business Excel" since 2016. He shares useful tips from his own experiences to help others improve their Excel skills and careers.
