Index/Match Formula - No Match to show Blank
I've been skimming the community here to try and find the IFERROR formula to add to my formula below so that if there is no match, a blank cell populates instead of #NO MATCH. The end goal is to populate a blank row so that users can put the right details into this cell from their reports. NO MATCH doesn't allow this.
Here's my formula; any help is appreciated!
=INDEX({Tracker Source Sheet}, MATCH([School/Task]@row, {Sheet where there is no match showing}, 0))
Best Answers
-
You should be able to manually type over the #NO MATCH error then. But if you would prefer a blank instead, then the IFERROR would be incorporated like so...
=IFERROR(INDEX({Tracker Source Sheet}, MATCH([School/Task]@row, {Sheet where there is no match showing}, 0)), "")
-
@Triggerfish So the way the IFERROR works is it will run whatever you put in the first section. If the first section throws an error, it will run/output whatever you have in the second section.
=IFERROR(original_formula, "")
So what this does is it will run your formula first. If there is a match and no error is thrown then it will output whatever the result of your INDEX/MATCH is. If there is an error thrown because there is no match then it will output the blank in the second portion of the function.
Answers
-
Are you using this as a column formula? If so, you will not be able to manually enter data into the cell even if a blank is output.
If you are not using it as a column formula, then the error should not block manual entry.
-
@Paul Newcome - I am not using this as a column formula. Any help is appreciated!
-
You should be able to manually type over the #NO MATCH error then. But if you would prefer a blank instead, then the IFERROR would be incorporated like so...
=IFERROR(INDEX({Tracker Source Sheet}, MATCH([School/Task]@row, {Sheet where there is no match showing}, 0)), "")
-
Thank you!
-
Happy to help. 👍️
-
@Paul Newcome Hi Paul. I'm really struggling to understand 'IFERROR' function, is there any chance you could please help me with this one? I get so confused with where exactly to add the IFERROR "" in my formula. This is what I currently have.... have tried many different ways by looking at your example above but it keeps returning my error. I simply want the IFERROR to return a blank cell instead of #NO MATCH. Hope you can help. Many thanks
=INDEX({TE MASTER TRACKER OF ALL NEW BRIEFS SUBMIT Range 1}, MATCH([Project Name]@row, {TE MASTER TRACKER OF ALL NEW BRIEFS SUBMIT Range 2}, 0))
The above formula is working fine and returning what I need. I just want to return a blank cell wherever there is no match found. 😊
-
@Triggerfish So the way the IFERROR works is it will run whatever you put in the first section. If the first section throws an error, it will run/output whatever you have in the second section.
=IFERROR(original_formula, "")
So what this does is it will run your formula first. If there is a match and no error is thrown then it will output whatever the result of your INDEX/MATCH is. If there is an error thrown because there is no match then it will output the blank in the second portion of the function.
-
@Paul Newcome and again you have answered EXACTLY what I needed in the moment. Great community, Great contributors. THANKS
-
Follow up question:
My original formula is:
=INDEX({Report - June Range 1}, MATCH([App/Channel]@row, {Report Project Name}, 0))
I would like it to return a blank cell if no match. Would my formula be:
=INDEX({Report - June Range 1}, MATCH([App/Channel]@row, {Report Project Name}, 0, IFERROR(INDEX({Report - June Range 1}, MATCH([App/Channel]@row, {Report Project Name}, "")))))
-
@Nana250 You would wrap the whole thing in the IFERROR the same way that it is in the accepted answers.
Help Article Resources
Categories
Check out the Formula Handbook template!
