Index/Match Formula - No Match to show Blank

JenS

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))

Paul Newcome

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)), "")

Paul Newcome

@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.

https://help.smartsheet.com/function/iferror

Paul Newcome

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.

JenS

@Paul Newcome - I am not using this as a column formula. Any help is appreciated!

Paul Newcome

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)), "")

JenS

Thank you!

Paul Newcome

Happy to help. 👍️

Triggerfish

@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. 😊

Paul Newcome

@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.

https://help.smartsheet.com/function/iferror

PeggyLang

@Paul Newcome and again you have answered EXACTLY what I needed in the moment. Great community, Great contributors. THANKS

Nana250

@Paul Newcome

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}, "")))))

Paul Newcome

@Nana250 You would wrap the whole thing in the IFERROR the same way that it is in the accepted answers.