IFERROR and INDEX/MATCH formula with criteria?

I am using this formula currently on a work document (slightly modified):

=IFERROR(INDEX({Assigned Specialist}, MATCH([Project Name]@row, {Project Title}, 0)), "")

Now, what I would like to add is a criteria to this formula that checks the other sheet (the same sheet that Assigned Specialist is on) to see if the Project Status is Complete or Disabled, and if it is, then to just return "". Essentially, I only want the formula to return the Assigned Specialist name if the project is still active.

Is that possible?

Find more posts tagged with

Formulas

Accepted answers

Paul Newcome

You need to wrap the whole thing in an IFERROR.

=IFERROR(original_formula, "")

All comments

Paul Newcome

You would use another INDEX/MATCH to pull in the status and then drop that in an IF/AND statement to say that if it is not "Complete" and not "Disabled" then output the Assigned Specialist INDEX/MATCH.

=IF(AND(status_index_match <> "Complete", status_index_match <> "Disabled"), assigned_index_match)

Jerexify

https://community.smartsheet.com/discussion/comment/390898#Comment_390898

Where would that go in the formula above? Or would it be a complete rewrite?

Base on what you said, and my limited understanding of the formulas (since most of what I do is pretty basic and a lot of them have come from these forums) I'm imagining it like this:

=IFERROR(IF(AND({Project Status}, <>"Complete", {Project Status}, <>"Disabled"), INDEX({Assigned Specialist}, MATCH([Project Name]@row, {Project Title}, 0)), "")

Does that look right?

Paul Newcome

Not quite. You would write a second INDEX/MATCH similar to the one you have for assigned specialist but bringing over the Status. Then you would drop each of the IDNEX/MATCH formulas into the indicated places within the IF formula structure previously provided.

Jerexify

https://community.smartsheet.com/discussion/comment/390916#Comment_390916

I think I understand, but I just want to make sure. I apologize for the never-ending questions.

If I understand correctly, using the formulas below:

assigned_index_match = INDEX({Assigned Specialist}, MATCH([Project Name]@row, {Project Title}, 0))

status_index_match = INDEX({Project Status}, MATCH([Project Name]@row, {Project Title}, 0))

it would look like this:

=IF(AND(INDEX({Project Status}, MATCH([Project Name]@row, {Project Title}, 0)), <>"Complete", INDEX({Project Status}, MATCH([Project Name]@row, {Project Title}, 0)), <>"Disabled"), INDEX({Assigned Specialist}, MATCH([Project Name]@row, {Project Title}, 0)), "")

Is that more accurate?

Paul Newcome

Yes. That looks right.

Jerexify

https://community.smartsheet.com/discussion/comment/390944#Comment_390944

So, I'm not sure if you'll be able to help without the sheet itself, but what I'm getting using the formula above is that, in the cells where a name should be returned, I get INVALID DATA TYPE, and in the cells where a name should not be returned, I get NO MATCH.

Any ideas what I may have done wrong?

Paul Newcome

Sorry. I just noticed two commas that need removed. You should not have a comma between the first and second part of a "logical statement" (inside of the AND function).

You have

=IF(AND(index_match, <> "Complete", .....

You need to remove the comma after the INDEX/MATCH. Same goes for the second one within the AND function.

=IF(AND(index_match <> "Complete", .....

Jerexify

https://community.smartsheet.com/discussion/comment/391076#Comment_391076

Ah, gotcha! OK, I think I'm almost there. It's a work document that is live, so I'm unable to apply and leave the formula until I have it correct.

When I tried it this time, it appears to have worked, but I was getting NO MATCH for a lot of cells. It is technically correct, but is there a way to make it just return nothing instead of NO MATCH? I had thought that's what the last empty quotation set was for, but it doesn't seem to have worked.

Thanks again for all of your help!

Paul Newcome

You need to wrap the whole thing in an IFERROR.

=IFERROR(original_formula, "")

Jerexify

https://community.smartsheet.com/discussion/comment/391096#Comment_391096

That worked!

Thank you so much for all of your help.

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.