Explanation
In this example, the goal is to perform a two-way lookup based on the name in cell H4 and the month in cell H5 with the VLOOKUP function. Inside the VLOOKUP function, the column index argument is normally hard-coded as a static number. However, you can create a dynamic column index number by using the MATCH function to locate the right column. This technique allows you to create a dynamic two-way lookup, matching on both rows and columns. It can also make a VLOOKUP formula more resilient. VLOOKUP can break when columns are inserted or removed from a table, but a formula that uses VLOOKUP + MATCH can continue to work correctly even changes are made to columns.
VLOOKUP formula
This is a standard VLOOKUP exact match formula with one exception: the column index is supplied by the MATCH function. If we remove the MATCH function, the core VLOOKUP formula to match the name in H4 and retrieve a value for February is:
=VLOOKUP(H4,B5:E16,3,0)
The lookup_value is the name in cell H4, the table_array is the range B5:E16, the col_index_num is hardcoded as 3 (to retrieve values from the “Feb” column), and range_lookup is set to 0 to force an exact match. (Note: you can use either zero or FALSE for range_lookup with the same result). With these inputs, the formula returns 9,350, the value for Colby in February.
MATCH formula
The goal in this example is to dynamically generate a column number for VLOOKUP based on the value in cell H5. To do this, we use the MATCH function like this:
MATCH(H5,B4:E4,0) // returns 3
The lookup_value is the month abbreviation in cell H5, the lookup_array is the range B4:E4, and match_type is set to zero (0) to specify an exact match. With this configuration, MATCH returns 3 since the matching value is in cell D4, which is the third value in the range B4:E4. Note that the lookup array given to MATCH (BB4:E4) representing column headers deliberately includes cell B4, even though cell B4 does not contain a month name. This is done so that the number returned by MATCH is in sync with the lookup table given by VLOOKUP. In other words, we need to give MATCH a range that spans the same number of columns as the lookup table given to VLOOKUP.
VLOOKUP + MATCH
The final formula with VLOOKUP and MATCH together is:
=VLOOKUP(H4,B5:E16,MATCH(H5,B4:E4,0),0)
Notice that the MATCH function is provided to VLOOKUP as the column index number . Excel evaluates the formula from the inside out. The MATCH function is evaluated and returns 3 directly to VLOOKUP as col_num_index :
=VLOOKUP(H4,B5:E16,3,0)
The VLOOKUP function then runs and returns a final result of 9,350. This is the value for Colby in February. Note that the month lookup is now dynamic. For example, if the month is changed to “Mar”, MATCH returns 4 and VLOOKUP returns a final result of 12,550.
Explanation
In this example, the goal is to calculate the correct commission for both Agent and Broker based on a 3% commission which is split according to the table in G7:I11, which is named split . Notice the amount going to the Agent and Broker changes as the total amount increases, which the Agent getting a larger share for higher amounts. The example assumes all commissions are 3%, as set in cell H4, the named range rate . This is a nice example of how the result from VLOOKUP can easily be used inside another formula.
Total commission
The total commission amount is calculated in cell C5 with this formula:
=B5*rate // total commission
Where rate is the named range H4. As the formula is copied down, a total commission is calculated for each amount in column B. If the commission rate in H4 is changed, all amounts will update.
Agent and Broker split
The Agent and Broker portion of the total commission shown in columns D and E are calculated like this:
=C5*VLOOKUP(B5,split,2,TRUE) // agent
=C5*VLOOKUP(B5,split,3,TRUE) // broker
Notice these formulas are identical except for the column index. The formula for Agent returns a value from column 2 in the table, and the formula for Broker returns a value from column 3 in the table. In both formulas, VLOOKUP is set to approximate match explicitly, by setting the optional fourth argument, range_lookup , to TRUE.
Note: When doing an approximate match, VLOOKUP assumes the table is sorted in ascending order. If VLOOKUP finds an exact match, it returns a value from the matching row. If VLOOKUP encounters a value greater than the lookup value, it will return a value from the previous row.
