Level 3ModelingPractice, 3 min
INDEX/MATCH on two conditions
The task
Each rep appears once per month, so neither column alone finds the row. In F3, return the revenue for the rep in F1 and the month in F2: match on both at once with
MATCH(1, (A2:A13=F1)*(B2:B13=F2), 0).The data
The starting sheet. Cells outlined in green are where your formulas go.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Rep | Month | Revenue | Rep | Priya | |
| 2 | Maya | Jan | $48,200 | Month | Mar | |
| 3 | Theo | Jan | $39,100 | Revenue | ||
| 4 | Priya | Jan | $52,700 | |||
| 5 | Maya | Feb | $44,900 | |||
| 6 | Theo | Feb | $41,800 | |||
| 7 | Priya | Feb | $50,300 | |||
| 8 | Maya | Mar | $51,600 | |||
| 9 | Theo | Mar | $36,400 | |||
| 10 | Priya | Mar | $57,900 | |||
| 11 | Maya | Apr | $47,300 | |||
| 12 | Theo | Apr | $42,600 | |||
| 13 | Priya | Apr | $55,100 |