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.
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.