You're a finance analyst computing this quarter's sales commissions. The commission rate depends on both region AND tier — there's no single-axis lookup that works.
Columns A–C contain 8 reps with their region and tier. Build the commission lookup using a composite key — concatenate region and tier inline in your formula and look up against a flat 8-row reference. No helper column allowed — the concatenation must happen inside the lookup.
Your task:
- Build the reference at G2:H9 with composite keys in column G and rates in H. Use the values below.
- Write a single formula in column D that returns the correct commission rate for each rep using
B2 & "-" & C2 as the lookup key.
Reference:
| Composite Key | Commission Rate |
|---|
| West-Bronze | 0.020 |
| West-Silver | 0.030 |
| West-Gold | 0.050 |
| East-Bronze | 0.025 |
| East-Silver | 0.035 |
| East-Gold | 0.060 |
| North-Silver | 0.040 |
| North-Gold | 0.055 |
Graded cells: D3, D5, D9.
Open this problem on a desktop to attempt it.