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?

Find more posts tagged with

Accepted answers

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.

All comments

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. 👍️

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.