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)), "")
Best Answer
-
Does this work?
=IF(PCN@row <> "", IFERROR(INDEX({Projected Enrollment}, MATCH([PCN]@row, {Enrollment - PCN}, 0)), ""))
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Answers
-
=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
Sincerely,
Jacob Stey
-
@SteyJ i am getting an error when I try your formula "#INCORRECT ARGUMENT SET"
-
@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.
-
@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)))
Sincerely,
Jacob Stey
-
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)), "")
-
Does this work?
=IF(PCN@row <> "", IFERROR(INDEX({Projected Enrollment}, MATCH([PCN]@row, {Enrollment - PCN}, 0)), ""))
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
Thank you very much Paul, your formula worked as a charm!
-
Happy to help. 👍️
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!