Excel allows us to lookup with dynamic column number parameter in the VLOOKUP function using MATCH function. This step by step tutorial will assist all levels of Excel users to do the two-way lookup.
Figure 1. Find value per store and week with VLOOKUP and MATCH functions
Syntax of the VLOOKUP formula
=VLOOKUP(lookup_value, table_array, col_index_num, range_lookup)
The parameters of the VLOOKUP function are:
- lookup_value – a value that we want to find in a table_array
- table_array – a range in which we want to lookup
- col_index_num – a column number in table_array from which we would like to get a value
- range_lookup – default value is FALSE. This means that we want to find an exact match for a lookup value.
Syntax of the MATCH formula
=MATCH(lookup_value, lookup_array, [match_type])
The parameters of the MATCH function are:
- lookup_value – a value which we want to find in the lookup_array
- lookup_array – the array where we want to find a value
- [match_type] – a type of match. We put 0 which is an exact match.
Setting up Our Data to Get the Value Based on the Two Criteria
Our first table consists of 4 columns: “Store” (column B), “Week 1” (column C), “Week 2” (column D) and “Week 3” (column E). The second one has the data for the Store and Week from the first table and an empty cell for the Value. The idea is to get the value from the first table based on the Store and the Week from the second table and to place the result in the cell H5.
Figure 2. Table structure for two-way lookup
Get the Value Based on Store and Week with VLOOKUP and MATCH Functions
We want to get the value from the first table based on the criteria from the column H for the Store and the Week. In order to make the formula more clear, we will create a named range Array_Vlookup for cell range B3:E8 and a named range Array_Match for the cell range B2:E2.
To create a named range we should follow the steps:
- Select the cell range that should be named
- Click on the name box in Excel
- Write the name for the cell range and press enter
Figure 3. Creating a named Array_Vlookup for the VLOOKUP function
Figure 4. Creating a named range Array_Match for the MATCH function
The formula looks like:
The lookup_value is the cell H3 “Store D” while the table_array is the named range Array_Vlookup. The parameter col_index_num is the formula
MATCH(H4,Array_Match,0) while the range_lookup is FALSE as we want the exact match.
The parameter lookup_value in the MATCH function is the cell H4 “Week 3” while the lookup_array is the named range Array_Match. The match_type is 0 since we want the exact match.
To apply the formula we need to follow these steps:
- Select cell H5 and click on it
- Insert the formula:
- Press enter
Figure 5. Two-way lookup with VLOOKUP and MATCH functions
The MATCH function gives us information about the col_index_num parameter of the VLOOKUP function. The result of the MATCH function is number 4 because “Week 3” is in the 4th position in the range Array_Match.
The VLOOKUP function gets the value from the Array_Vlookup with the lookup_value “Store D” and column number 4. Final formula result is the number $6,886, value for the “Store D” from the “Week 3”.
Most of the time, the problem you will need to solve will be more complex than a simple application of a formula or function. If you want to save hours of research and frustration, try our live Excelchat service! Our Excel Experts are available 24/7 to answer any Excel question you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.