## Explanation

This formula assumes all commissions are 3%, as set in cell H4, the named range **comm_rate**.

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:

```
=B5*comm_rate // total commission
```

The Agent and Broker amounts in columns D and E are calculated like this:

```
=C5*VLOOKUP(B5,split_table,2,TRUE) // agent
=C5*VLOOKUP(B5,split_table,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 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*.