Double lookup in multi column table

[maacmaac]

I have table of Sales reps and corresponding bonus structure for each person. Each rep is payed a bonus based on amount of sales. The bonus for reach Tier 1 is $5,000; the bonus for reaching Tier 2 is $50,000. The issue is each rep must achieve a different level of total sales to reach tier 1 and tier 2. I have attached a sample spreadsheet with actual sales for each rep and expected bonus for each. Thanks in advance for any comments.

[benishiryo]

hi maacmaac. it seems like Tier1 is 10,000 instead of 5,000? or is there something i dont understand? if you need to return the tier, try this in H2:
=IF(G2>=C2,2,IF(G2>=B2,1,0))

then in I2:
=CHOOSE(H2+1,0,10000,50000)

[Lemice]

Hello,

You can use this for column I

Please Login or Register  to view this content.

and for column H, I suggest using benishiryo's method, that's a really good one

Please Login or Register  to view this content.

And here's the sample.

[maacmaac]

That works fine with the Sumproduct. Is there a way to eliminate having to use column H ("Tier Reached") and incorporate into column I? Column H was only there for illustrative purposes and not part of actual spreadsheet model? Attached is updated example. Thanks for all comments.

[Lemice]

Well, you can put straight the formula in column H into the CHOOSE, like

Please Login or Register  to view this content.

Here is your ver 03 with the formula.