I have the following table below and I'm trying to use INDEX:MATCH:MATCH, but I'm having a challenge searching between the two different ranges.
In Cell B1 user would input the number of units
In Cell B2 User would input the annual sales.
In cell B3 I need a formula that would look at cell B1 and look at the ranges in rows 5 and 6 to determine which column to lookup and also look at cell B2 to look at the range in column A and B to determine which row to lookup.
In the example below based on 66 and 432,000 it would return the value in Cell I9 which is 3.45%
In Cell B1 user would input the number of units
In Cell B2 User would input the annual sales.
In cell B3 I need a formula that would look at cell B1 and look at the ranges in rows 5 and 6 to determine which column to lookup and also look at cell B2 to look at the range in column A and B to determine which row to lookup.
In the example below based on 66 and 432,000 it would return the value in Cell I9 which is 3.45%
| Book1 | ||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | |||
| 1 | # of Units | 66.00 | ||||||||||||||||
| 2 | Annual Sales | 432,000.00 | ||||||||||||||||
| 3 | Percentage | 3.45% | ||||||||||||||||
| 4 | ||||||||||||||||||
| 5 | 0.00 | 3.00 | 5.00 | 10.00 | 20.00 | 40.00 | 60.00 | 80.00 | 100.00 | 120.00 | 140.00 | 160.00 | 180.00 | 200.00 | ||||
| 6 | 2.00 | 4.00 | 9.00 | 19.00 | 39.00 | 59.00 | 79.00 | 99.00 | 119.00 | 139.00 | 159.00 | 179.00 | 199.00 | 100000.00 | ||||
| 7 | 0.00 | 349999.99 | Custom | Custom | Custom | 3.95% | 3.95% | 3.65% | 3.65% | 3.45% | 3.45% | 3.45% | 3.45% | 3.45% | 3.45% | 3.45% | ||
| 8 | 350000.00 | 424999.99 | Custom | Custom | Custom | 3.95% | 3.95% | 3.65% | 3.45% | 3.45% | 3.45% | 3.45% | 3.45% | 3.45% | 3.45% | 3.45% | ||
| 9 | 425000.00 | 499999.99 | Custom | Custom | Custom | 3.95% | 3.65% | 3.65% | 3.45% | 3.45% | 3.45% | 3.45% | 3.45% | 3.45% | 3.45% | 3.45% | ||
| 10 | 500000.00 | 599999.99 | Custom | Custom | 3.95% | 3.95% | 3.65% | 3.45% | 3.45% | 3.45% | 3.45% | 3.45% | 3.45% | 3.45% | 3.45% | 3.45% | ||
| 11 | 600000.00 | 699999.99 | Custom | Custom | 3.95% | 3.95% | 3.65% | 3.45% | 3.45% | 3.45% | 3.45% | 3.45% | 3.45% | 3.45% | 3.45% | 3.45% | ||
| 12 | 700000.00 | 799999.99 | Custom | Custom | 3.95% | 3.95% | 3.65% | 3.45% | 3.45% | 3.45% | 3.45% | 3.45% | 3.45% | 3.45% | 3.45% | 3.45% | ||
| 13 | 800000.00 | 899999.99 | Custom | Custom | 3.95% | 3.65% | 3.45% | 3.45% | 3.45% | 3.45% | 3.45% | 3.45% | 3.45% | 3.45% | 3.45% | 3.45% | ||
| 14 | 900000.00 | 999999.99 | Custom | Custom | 3.95% | 3.65% | 3.45% | 3.45% | 3.45% | 3.45% | 3.45% | 3.45% | 3.45% | 3.45% | 3.45% | 3.45% | ||
| 15 | 1000000.00 | 100000000.00 | Custom | 3.95% | 3.95% | 3.65% | 3.45% | 3.45% | 3.45% | 3.45% | 3.45% | 3.45% | 3.45% | 3.45% | 3.45% | 3.45% | ||
Sheet1 | ||||||||||||||||||
