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?
Best Answer
-
You need to wrap the whole thing in an IFERROR.
=IFERROR(original_formula, "")
Answers
-
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)
-
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?
-
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.
-
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?
-
Yes. That looks right.
-
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?
-
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", .....
-
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!
-
You need to wrap the whole thing in an IFERROR.
=IFERROR(original_formula, "")
-
That worked!
Thank you so much for all of your help.
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 471 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!
