How do you SKIP a BLANK cell when using an INDEX / MATCH formula?

This is a working formula to pull values from another sheet. The issue is that on the SOURCE sheet, when the "PCN" is blank, they are showing a default text under the "Enrollment - PCN" column. That works perfectly for the SOURCE sheet, but is messing the automations on MY (destination) sheet.

=IFERROR(INDEX({Projected Enrollment}, MATCH([PCN]@row, {Enrollment - PCN}, 0)), "")

How do I modify the above working formula to skip the INDEX/MATCH is the "PCN" column in MY sheet is blank?

I have tried the following two formula variations, but they do not work :(

=IFERROR(IF([PCN]@row <> "", (INDEX({Projected Enrollment}, MATCH([PCN]@row, {Enrollment - PCN}, 0)), "")

and

=IFERROR(IF(ISBLANK([PCN]@row, "", (INDEX({Projected Enrollment}, MATCH([PCN]@row, {Enrollment - PCN}, 0)), "")

Find more posts tagged with

Accepted answers

Paul Newcome

Does this work?

=IF(PCN@row <> "", IFERROR(INDEX({Projected Enrollment}, MATCH([PCN]@row, {Enrollment - PCN}, 0)), ""))

All comments

SteyJ

https://community.smartsheet.com/discussion/109814/how-do-you-skip-a-blank-cell-when-using-an-index-match-formula

=IFERROR(IF(ISBLANK([PCN]@row, "", (INDEX({Projected Enrollment}, MATCH([PCN]@row, {Enrollment - PCN}, 0)), "")

Looks like the format is a little weird here.

Try this:

=IF(ISBLANK(PCN@row), "Blank", INDEX({Projected Enrollment}, MATCH(PCN@row, {Enrollment - PCN}, 0)))

If you need the IFERROR, try this:

=IFERROR(IF(ISBLANK(PCN@row), "Blank", INDEX({Projected Enrollment}, MATCH(PCN@row, {Enrollment - PCN}, 0))), "")

Hope this helps

Filippo

@SteyJ i am getting an error when I try your formula "#INCORRECT ARGUMENT SET"

Eric Law

@Filippo Can you provide some screen shots of both the source and destination sheets so we can see what your outputs are?

The IFERROR formula looks kind of weird since you are referencing another sheet, you can't just use PCN@row since PCN is on another sheet, unless you have PCN on your destination sheet.

SteyJ

https://community.smartsheet.com/discussion/comment/394064#Comment_394064

@Filippo are you able to share a screenshot of the sheet, which version of the formula are you using?

=IF(ISBLANK(PCN@row), "", INDEX({Projected Enrollment}, MATCH(PCN@row, {Enrollment - PCN}, 0)))

Filippo

The updated formula continues to give an #INCORRECT ARGUMENT SET error.

=IF(ISBLANK(PCN@row), "", INDEX({Projected Enrollment}, MATCH(PCN@row, {Enrollment - PCN}, 0)))

The sheets are very large and I have a lot of sensitive data i cannot share.

PCN@row - column is in the destination sheet.
Projected Enrollment - column is in the source sheet
Enrollment - PCN - column is in the source sheet

Both PCN@row and Enrollment - PCN columns have the same identifiers, which are PCNs

The issue is that the "Enrollment - PCN", in the source sheet, has a default text that displays when the PCN is blank. My goals is NOT to pull this text in my destination sheet. Because "PCN@row", in my destination sheet, has a list of PCNs, and at the bottom you have blank PCNs, it pulls the default text from the source sheet, since both sheets have blank PCNs....I would like to skip blank PCNs from my destination sheet, so it doesn't look for a match of blank PCNs in the source sheet.

As a reminder, my current formula works perfectly. I just need to exclude the INDEX/MATCH when the PCN@row is blank on my destination sheet.

Working formula that needs editing:

IFERROR(INDEX({Projected Enrollment}, MATCH([PCN]@row, {Enrollment - PCN}, 0)), "")

Paul Newcome

Does this work?

=IF(PCN@row <> "", IFERROR(INDEX({Projected Enrollment}, MATCH([PCN]@row, {Enrollment - PCN}, 0)), ""))

Filippo

Thank you very much Paul, your formula worked as a charm!

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.