CODEWITHSUNDEEP
excelexcel-formula
Aaron Keith
Aaron Keith

Reputation: 3

INDEX MATCH with Nested IF Statements

Here is my statement with just one IF Statement:

=IF(AF2="Consultant",IF(C2=INDEX(JIRA!F:F,MATCH('RFO Checks'!M2,JIRA!A:A,0)),1,0),"N/A")

This works great, but now I need to add two more IF Statements.

AF2 will either contain "Consultant", "Retailer", or "PC".

Each one will be directed to a different price column:

I've been wracking my brain for two days now and haven't gotten no where.

Suggestions?

Thank you in advance!

Upvotes: 0

Views: 9156

Answers (2)

Bharat Anand
Bharat Anand

Reputation: 484

Aaron,

I have provided you the high level structure for the change of logic.

If this is what you want, then you can substitute the phrases with the appropriate logic.

=IF(AF2="Consultant",
    IF(C2=INDEX(JIRA!F:F,MATCH('RFO Checks'!M2,JIRA!A:A,0)),1,0),
    IF(AF2="Retailer",
        <insert_logic_for_retailer>,
        IF(AF2="PC",<insert_logic_for_PC>,"N/A")
      )
   )

Logic for Consultant - 

    IF(C2=INDEX(JIRA!F:F,MATCH('RFO Checks'!M2,JIRA!A:A,0)),1,0)

Logic for Retailer, use this to replace - 

    IF(C2=INDEX(JIRA!D:D,MATCH('RFO Checks'!M2,JIRA!A:A,0)),1,0)

Logic for PC, use this to replace - 

    IF(C2=INDEX(JIRA!E:E,MATCH('RFO Checks'!M2,JIRA!A:A,0)),1,0)

Let me know in case you still have any issues!

Upvotes: 1

Scott Craner
Scott Craner

Reputation: 152660

Use CHOOSE()

=IFERROR(--(C2=INDEX(CHOOSE(MATCH(AF2,{"Consultant", "Retailer", "PC"},0),JIRA!F:F,JIRA!D:D,JIRA!E:E),MATCH('RFO Checks'!M2,JIRA!A:A,0))),"N/A")

Upvotes: 1

Related Questions