Using Index/Match for multiple criteria, if NO MATCH, how to return the remaining value instead?


Hi All,

I have a pretty robust INDEX/MATCH formula set up. It pretty much sums up values from 4 different smartsheets into a master one.

=INDEX({GB_WN1}, MATCH($Projects@row, {GB_PROJ}, 0)) + INDEX({AN_WN1}, MATCH($Projects@row, {AN_PROJ}, 0)) + INDEX({GS_WN1}, MATCH($Projects@row, {GS_PROJ}, 0)) + INDEX({AD_WN1}, MATCH($Projects@row, {AD_PROJ}, 0)) + INDEX({MM_WN1}, MATCH($Projects@row, {MM_PROJ}, 0))

ISSUE: If anyone deletes the reference value from their sheet, then the master sheet returns "NO MATCH" for the entire row.

Instead of NO MATCH, I wanted the cell to return whatever the remaining calculation is. I tried using IFERROR, but I don't want to define a value, simply want the sum to flow through. For example, if GS deletes 6 match values from their sheet then instead of 6 rows in the master sheet returning "NO MATCH," instead the rows would return the sum from GB, AN, AD, and MM.

Anyone have any ideas?

Best Answer


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!