where split_table is the named range G7:I11 The commission amount is split between the agent and broker, starting with a 50/50 split, and changing at fixed intervals as shown in the named range split_table (G7:I11). The commission amount is calculated in column C with this formula: The Agent and Broker amounts in columns D and E are calculated like this: 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 providing the optional fourth argument as TRUE. 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 matching row. If VLOOKUP encounters a value greater than the lookup value, it will return a value from the previous row.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
