Lookup Value Based on Multiple Criteria (one of which, not exact)

[xtinct2]

Hi --

Some of my clients have a tiered commission rate structure depending on spend amounts. I'm hoping to house all the commission rate structures in one table, and return the appropraite commission rate based off multiple criteria (one of which being a number within a range).

I attached an excel file that get's me what I need if I was only looking at one crieteria (whether the designated spend amount is within a range -- ish, you'll see) -- formula in E4.
However, I'd like to base this formula off columns B & C as well, since i plan on inputting multiple clients/projects into one table.

Is this possible?

Thanks!

[CAntosh]

Try the formula below in E4. It should be array-entered (confirmed with Ctrl + Shift + Enter instead of Enter):

=IFERROR(INDEX(COMM_TABLE[% of Media],MATCH(1,(COMM_TABLE[Client]=B4)*(COMM_TABLE[Project Code]=C4)*(COMM_TABLE[Min Spend]<=D4)*(COMM_TABLE[Max Spend]>=D4),0)),"No Match")

It should return the match that fits the choices in B4:D4.


EDIT: Here's an alternative that addresses the "And Up" text a bit more explicitly. It should also be array-entered.

=IFERROR(INDEX(COMM_TABLE[% of Media],MATCH(1,(COMM_TABLE[Client]=B4)*(COMM_TABLE[Project Code]=C4)*(COMM_TABLE[Min Spend]<=D4)*((COMM_TABLE[Max Spend]>=D4)+(NOT(ISNUMBER(COMM_TABLE[Max Spend])))>0),0)),"No Match")

[xtinct2]

Try the formula below in E4. It should be array-entered (confirmed with Ctrl + Shift + Enter instead of Enter):

=IFERROR(INDEX(COMM_TABLE[% of Media],MATCH(1,(COMM_TABLE[Client]=B4)*(COMM_TABLE[Project Code]=C4)*(COMM_TABLE[Min Spend]<=D4)*(COMM_TABLE[Max Spend]>=D4),0)),"No Match")

It should return the match that fits the choices in B4:D4.


EDIT: Here's an alternative that addresses the "And Up" text a bit more explicitly. It should also be array-entered.

=IFERROR(INDEX(COMM_TABLE[% of Media],MATCH(1,(COMM_TABLE[Client]=B4)*(COMM_TABLE[Project Code]=C4)*(COMM_TABLE[Min Spend]<=D4)*((COMM_TABLE[Max Spend]>=D4)+(NOT(ISNUMBER(COMM_TABLE[Max Spend])))>0),0)),"No Match")

ahhh! this is beautiful
thank you!!!

[CAntosh]

Glad to help, good luck!