Multiple If Statements not working?

patkyle

Hello,

I'm trying to setup a multiple IF statement but I'm encountering an error where the 2nd multiple if statement does not run. The first statement runs correctly but the subsequent statements always fail.

The Formula:

=IF(INDEX({Area 1 (AMER) QC sheet Range 4}, MATCH([SIM T: Customer Request]@row, {Area 1 (AMER) QC sheet Range 3}), 31) = "Yes", INDEX({Area 1 (AMER) QC sheet Range 4}, MATCH([SIM T: Customer Request]@row, {Area 1 (AMER) QC sheet Range 3}), 31), IF(INDEX({Area 1 (AMER) QC sheet Range 5}, MATCH([SIM T: Customer Request]@row, {Area 1 PS Sim Ticket}), 32) = "Yes", INDEX({Area 1 (AMER) QC sheet Range 5}, MATCH([SIM T: Customer Request]@row, {Area 1 PS Sim Ticket}), 32), "No"))

The ask:

I need to verify that work has been completed in 7 different sheets against 1 master sheet.

Is anyone able to help? I'm not sure what I'm missing

Lucas Rayala

hi @patkyle I think what’s happening is that when you use the INDEX/MATCH as the evaluation criteria, when it doesn’t find a match it errors out. You could do a few things. Easiest may be to wrap the INDEX/MATCH in an IFERROR statement, with the error being “No”.

=IF(IFERROR(INDEX({Area 1 (AMER) QC sheet Range 4}, MATCH([SIM T: Customer Request]@row, {Area 1 (AMER) QC sheet Range 3}), 31),”No”) = "Yes", etc.

However, perhaps cleaner would be to replace that INDEX/MATCH with a COUNTIFS, which would return a count of 0 if it didn’t find anything, instead of erroring out. It would look something like:

=IF(COUNTIFS(<your criteria>)>0, etc