Multiple Index(match IFERRORs

[Kmitchell]

Hello, I need some guidance on executing this formula. Essentially if there is no match on the 2020 Contracting increases schedule we then return a value on the trigger tab, if there is a match on the 2020 tab but there is no value it should search for the match on the 2019 contracting increases schedule, then if there is a match in 2019 but the match = 0 it will return the value in the trigger tab.

This formula below is my failed attempt. Suggestions?

IFERROR(IF(INDEX('2020 CONTRACTING INCREASES '!$G$4:$G$65,MATCH($A18,'2020 CONTRACTING INCREASES '!$A$4:$A$65,0))=0,'TRIGGER - INTERNAL ONLY'!$J$14,index('2019 CONTRACTING INCREASES'!$G$4:$G$65,match('SB_Inpatient-2020'!$A18,'2019 CONTRACTING INCREASES'!$A$4:$A$65,0))=0,'TRIGGER - INTERNAL ONLY'!$J$14)))

 

[tyija1995]

Hey,

This one is quite tricky without seeing the data but here's my go at the issue:

IFERROR(IF(INDEX('2020 CONTRACTING INCREASES '!$G$4:$G$65,MATCH('SB_Inpatient-2020'!$A18,'2020 CONTRACTING INCREASES '!$A$4:$A$65,FALSE),1)=0,'TRIGGER - INTERNAL ONLY'!J14,INDEX('2020 CONTRACTING INCREASES '!$G$4:$G$65,MATCH('SB_Inpatient-2020'!$A18,'2020 CONTRACTING INCREASES '!$A$4:$A$65,FALSE),1)),IF(INDEX('2019 CONTRACTING INCREASES'!G4:G65,MATCH('SB_Inpatient-2020'!$A18,'2019 CONTRACTING INCREASES'!$A$4:$A$65,FALSE),1)=0,'TRIGGER - INTERNAL ONLY'!J14,INDEX('2019 CONTRACTING INCREASES'!$G$4:$G$65,MATCH('SB_Inpatient-2020'!$A18,'2019 CONTRACTING INCREASES'!$A$4:$A$65,FALSE),1)))

Let me know if that works!

 

[Kmitchell]

Thank you so much for sending this along! It works but is not returning the expected result. I tried to move the formula around but was not successful. I think its how I explained it

Essentially it should look to the 2020 schedule first and if there is no match at all then it should go to the trigger, if there is a match and it =0 then it should go to the 2019 schedule. If there is a match in 2019 and it is also =0 then it should default to the trigger schedule.

The 2020 and 2019 schedules have the same list its just that 2020 may not have rates and 2019 might have them and we would want to capture that.

Does that help?

 

[tyija1995]

Hey,

OK i'll take a look in to it - I think I can see the problem so let me re-work the formula and get back to you!

 

[tyija1995]

Hey,

Try this (I hope this works!):

IFNA(IF(INDEX('2020 CONTRACTING INCREASES '!$G$4:$G$65,MATCH('SB_Inpatient-2020'!$A18,'2020 CONTRACTING INCREASES '!$A$4:$A$65,FALSE),1)=0,IF(INDEX('2019 CONTRACTING INCREASES'!$G$4:$G$65,MATCH('SB_Inpatient-2020'!$A18,'2019 CONTRACTING INCREASES'!$A$4:$A$65,FALSE),1)=0,'TRIGGER - INTERNAL ONLY'!$J$14,INDEX('2019 CONTRACTING INCREASES'!$G$4:$G$65,MATCH('SB_Inpatient-2020'!$A18,'2019 CONTRACTING INCREASES'!$A$4:$A$65,FALSE),1)),INDEX('2020 CONTRACTING INCREASES '!$G$4:$G$65,MATCH('SB_Inpatient-2020'!$A18,'2020 CONTRACTING INCREASES '!$A$4:$A$65,FALSE),1)),'TRIGGER - INTERNAL ONLY'!$J$14)

 

[Kmitchell]

OMG Amazing! Worked like a charm! Thank you so much!

 

[Kmitchell]

Hi ! I caught something with my file and need to add in another stipulation, however I tried to follow the formula provided and it is not giving an expected result.

Here is what I attempted to do, it is returning 0% in this example and it should have found 2.5%.

Essentially we want it to look at 2020 file column H first, and if its blank go to 2019 and look at column AA and if blank go to 2019 and look at column G, if they are all blank then go to the trigger tab, if it is not found at all then go to the trigger. What am I doing wrong here? The only time it should be 0% is when it is hard coded into the 2019 or 2020 templates.

=IFNA(IF(INDEX('2020 CONTRACTING INCREASES '!$H$4:$H$65,MATCH($A49,'2020 CONTRACTING INCREASES '!$A$4:$A$65,FALSE),1)="",IF(INDEX('2019 CONTRACTING INCREASES'!$AA$4:$AA$65,MATCH($A49,'2019 CONTRACTING INCREASES'!$A$4:$A$65,FALSE),1)="",IF(INDEX('2019 CONTRACTING INCREASES'!$G$4:$G$65,MATCH($A49,'2019 CONTRACTING INCREASES'!$A$4:$A$65,FALSE),1)="",'TRIGGER - INTERNAL ONLY'!$I$14,INDEX('2020 CONTRACTING INCREASES '!$H$4:$H$65,MATCH($A49,'2020 CONTRACTING INCREASES '!$A$4:$A$65,FALSE),1)),INDEX('2019 CONTRACTING INCREASES'!$AA$4:$AA$65,MATCH($A49,'2019 CONTRACTING INCREASES'!$A$4:$A$65,FALSE),1)),INDEX('2019 CONTRACTING INCREASES'!$G$4:$G$65,MATCH($A49,'2019 CONTRACTING INCREASES'!$A$4:$A$65,FALSE),1)),'TRIGGER - INTERNAL ONLY'!$I$14)

 

[tyija1995]

Hey,

So after having a look through at this formula, which in itself is quite complicated to read! I think I have come up with something to meet your situation:

IFNA(IF(INDEX('2020 CONTRACTING INCREASES '!$H$4:$H$65,MATCH($A49,'2020 CONTRACTING INCREASES '!$A$4:$A$65,FALSE),1)="",IFNA(IF(INDEX('2019 CONTRACTING INCREASES'!$AA$4:$AA$65,MATCH($A49,'2019 CONTRACTING INCREASES'!$A$4:$A$65,FALSE),1)="",IFNA(IF(INDEX('2019 CONTRACTING INCREASES'!$G$4:$G$65,MATCH($A49,'2019 CONTRACTING INCREASES'!$A$4:$A$65,FALSE),1)="",'TRIGGER - INTERNAL ONLY'!I14,INDEX('2019 CONTRACTING INCREASES'!$G$4:$G$65,MATCH($A49,'2019 CONTRACTING INCREASES'!$A$4:$A$65,FALSE),1)),'TRIGGER - INTERNAL ONLY'!$I$14),INDEX('2019 CONTRACTING INCREASES'!$AA$4:$AA$65,MATCH($A49,'2019 CONTRACTING INCREASES'!$A$4:$A$65,FALSE),1)),'TRIGGER - INTERNAL ONLY'!$I$14),INDEX('2020 CONTRACTING INCREASES '!$H$4:$H$65,MATCH($A49,'2020 CONTRACTING INCREASES '!$A$4:$A$65,FALSE),1)),'TRIGGER - INTERNAL ONLY'!$I$14)

I used helper cells for each index match then pasted it carefully in to that master formula - let me know if it works. Cheers!