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

G B

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?

Paul Newcome

I am leaning towards an IFERROR based solution. If you wrap each INDEX/MATCH in its own IFERROR and have the IFERROR output a zero, then it should work for you.

Ayelet Weiner

I think I spoke too soon and was completely wrong in regards to the IFERROR.

Looking into it further, It looks like other's have wrapped their Index/match formula's USING SUMIF / COLLECT to return a value.

Paul Newcome

I am leaning towards an IFERROR based solution. If you wrap each INDEX/MATCH in its own IFERROR and have the IFERROR output a zero, then it should work for you.

G B

@Paul Newcome That solution worked! I was probably overthinking it.

Solution below for reference.

=IFERROR(INDEX({GB_WN2}, MATCH($Projects@row, {GB_PROJ}, 0)), 0) + IFERROR(INDEX({AN_WN2}, MATCH($Projects@row, {AN_PROJ}, 0)), 0) + IFERROR(INDEX({GS_WN2}, MATCH($Projects@row, {GS_PROJ}, 0)), 0) + IFERROR(INDEX({AD_WN2}, MATCH($Projects@row, {AD_PROJ}, 0)), 0) + IFERROR(INDEX({MM_WN2}, MATCH($Projects@row, {MM_PROJ}, 0)), 0)

Paul Newcome

Happy to help. 👍️