Struggling with IFERROR syntax in nested formula

ElyseMN

I've finally gotten this INDEX/MATCH formula to search 2 sheets and return the correct result but I'm struggling with the IFERROR portion. I think I need a second nested IFERROR. I would like the cell to remain empty if no result is found instead of the #NO MATCH result that currently appears.

=IFERROR(INDEX({Clinical Operations_ICS Range 7}, MATCH([Primary Column]@row, {Clinical Operations_ICS Range 2}, 0)), INDEX({Clinical Operations_ECS Range 1}, MATCH([Primary Column]@row, {Clinical Operations_ECS Range 7}, 0)))

SmartLew

at the moment, your second Index match is being treated as the "value if error". When you edit the formula and click on the second index match the "value if error" in the help tab will probably be highlighted yellow.

What are you trying to achieve with the two INDEX/MATCH Formulas side by side? Is it if the first one returns no match then use the second one?

ElyseMN

Yes, the two sheets are "Clinical Operations_ICS" and "Clinical Operations_ECS." Each sheet is a list of studies with columns for study information and dates. I'm using the formula to combine the two lists onto one sheet and pull over select study details. The formula is working but for some cells there is no data and instead of displaying #NO MATCH I would like the cell to be empty. I just can't seem to add a nested IFERROR and get the , "0")) in the correct place.

SmartLew

=IFERROR(INDEX({Clinical Operations_ICS Range 7}, MATCH([Primary Column]@row, {Clinical Operations_ICS Range 2}, 0)), INDEX({Clinical Operations_ECS Range 1}, MATCH([Primary Column]@row, {Clinical Operations_ECS Range 7}, 0)))

The way your formula is set up, the bit in Bold is being returned if there is an error. Therefore if the first index match returns an error, your formula is then just carrying out the second index match, if the result of this is no match, then that will be what is returned.

At the moment you have two index match formulas just placed next to each other, is below the process you want?

1. First index match looking at Clinical Operations ICS
2. If first index match returns a value, then thats the value in the cell
3. If the first index match returns NO MATCH, use the second INDEX MATCH looking at Clinical operations ECS
4. If this second INDEXMATCH also returns no MATCH, then return blank cell

If the above is right, you need this formula

=IFERROR(INDEX({Clinical Operations_ICS Range 7}, MATCH([Primary Column]@row, {Clinical Operations_ICS Range 2}, 0)), IFERROR(INDEX({Clinical Operations_ECS Range 1}, MATCH([Primary Column]@row, {Clinical Operations_ECS Range 7}, 0)),""))

Does that help?