Returns a single result based on multiple criteria

Hello,

Good day. I am trying to extract a specific prize for our raffle. The prize that a customer get depends on the combination of purchased items. We have 38 prizes to give away thus 38 combinations of items. I just can't seem to make this formula work =INDEX($P$3:$P$40,MATCH(1,(B2>=$I$3:$I$40)*(B2<$J$3:$J$40)*(C2>=$K$3:$K$40)*(D2>$L$3:$L$40)*(D2<$M$3:$M$40)*(E2>$N$3:$N$40)*(E2<$O$3:$O$40),0)). I am also thinking of using a nested if but 38 combinations might be too long for this. Any suggestions please. Many thanks.

User ITEM A ITEM B ITEM C ITEM D Prize
A 16.000 0.00 0.00 3.00 #N/A
B 0.000 100.00 0.00 0.00
C 204.000 0.00 283.00 1.00
D 0.000 0.00 0.00 0.00

Combination Table:

ITEM A ITEM B ITEM C ITEM D PRIZES
Min Max Min Max Min Max
1 80 0 1 16 0 0 1st Prize
80 290 0 1 16 0 0 2nd Prize
80 290 0 16 100 0 0 3rd Prize
80 290 0 1 16 0 1 4th Prize
80 290 0 1 16 1 2 5th Prize
80 290 0 1 16 2 3 6th Prize
80 290 90 1 16 3 4 7th Prize

Thanks for the quick reply pjwhitfield. What I actually need to have as a result is when a customer buys Item A (1-80pcs), Item B (0pc), Item C (0-16pcs) and Item D(0) the result would be 1st Prize. In the above example, User A bought 16pcs of Item A, 0pc of Item B, 0pc Item C and 3pcs Item D, the result should be 1st Prize and so on.

Do we therefore need a list of the 38 combinations ?

hello guys, thanks for the replies. Am attaching a sample file complete with 38 criteria and the expected result. Again thanks for looking into this.

That was fast JohnTopley! Will look into the file you sent. Thanks!