INDEX MATCH with Nested IF Statements

Question

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:

for "Consultant" it's JIRA!F:F
for "Retailer" it's JIRA!D:D
for "PC", it's JIRA!E:E.

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

Suggestions?

Thank you in advance!

Thank you GalAbra for your help.. it worked.

Aaron Keith
– Aaron Keith

2018-02-14 00:06:28 +00:00
Commented Feb 14, 2018 at 0:06 — Aaron Keith
– Aaron Keith, Commented Feb 14, 2018 at 0:06

Scott Craner · Accepted Answer · 2018-02-13 23:41:12Z

1

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")

answered Feb 13, 2018 at 23:41

Scott Craner

153k10 gold badges52 silver badges88 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Bharat Anand · Accepted Answer · 2018-02-13 23:47:53Z

1

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!

answered Feb 13, 2018 at 23:47

Bharat Anand

4841 gold badge6 silver badges15 bronze badges

4 Comments

Scott Craner Over a year ago

In using 'IF' I would flip the IF inside the INDEX: =INDEX(IF(AF2="Consultant",JIRA!F:F,IF(AF2="Retailer",JIRA!D:D,...))),MATCH...) It would eliminate the duplicate typing and shorten the formula.

Bharat Anand Over a year ago

@ScottCraner, not only your solutions are amazing, you yourself are a very nice person too. Thanks for your insights and may god bless you!

Scott Craner Over a year ago

You are too kind. I am just here to help.

Aaron Keith Over a year ago

Thank you for all your help!

Collectives™ on Stack Overflow

INDEX MATCH with Nested IF Statements

2 Answers 2

Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related