I am hoping someone can help with this problem. I am looking to fill in column d(yellow) with payouts based on a performance% being within a range(columns H&I). Normally I would do this by using index(J:J,match(c4,H:H,1)) but, I need to look up multiple scales in this scenario (ie. receiving and shipping). I have tried doing this as an array index(g4:J21, match(1,(b4=g:g)*(c4=h:h),1),4) but, I am not having success with this. I think the problem is I need to exact match b4 to column g while also doing a less than match for c4 to column h. The only other way I can think of is separating the tables and having nested if statements but, I have about 28 different scales to reference. Thank you for any help.
Table lookup question.jpg
Bookmarks