Sample= Attachment 255675
I have a sheet where I am trying to find a corresponding bonus percent if two conditions are met. The first condition is the area (Canada or USA) and the other criteria is the number of widgets sold. I have the results, but am unable to develop a formula that will look at the area (col A) and the widgets sold (col B) and provide the bonus payout percent (col C).
For the purposes of this project, I cannot change the formatting or layout. Any help would be much appreciated. This is the formula I'm using, but it only works if the results are exact... I need the formula to pull the closest (but not over) result.
=INDEX($C$2:$C$21,SUMPRODUCT(($A$2:$A$21=A26)*($B$2:$B$21=B26)*ROW($C$2:$C$21)),-1)
this doesn't work either:
=INDEX(C2:C21,MATCH(1,(A2:A21=A26)*(B2:B21=B26)),-1)
Canada 130 10
Canada 133 20
Canada 136 30
Canada 139 40
Canada 142 50
Canada 145 60
Canada 148 70
Canada 151 80
Canada 154 90
Canada 157 100
USA 621 10
USA 636 20
USA 651 30
USA 666 40
USA 681 50
USA 696 60
USA 711 70
USA 726 80
USA 741 90
USA 756 100
USA 750 <--- should result in 90
Canada 150 <--- should result in 70
Bookmarks