Index/Match Formula - No Match to show Blank

JenS · 01/11/22

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 · 01/11/22

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 · 10/12/22

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

IFERROR Function | Smartsheet Learning Center

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

Returns the first value if it isn’t an error and otherwise returns the second value

Paul Newcome · 01/11/22

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 · 01/11/22

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

Paul Newcome · 01/11/22

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 · 01/12/22

Thank you!

Paul Newcome · 01/12/22

Happy to help. 👍️

Triggerfish · 10/11/22

@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 · 10/12/22

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

IFERROR Function | Smartsheet Learning Center

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

Returns the first value if it isn’t an error and otherwise returns the second value

PeggyLang · 02/17/23

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

Nana250 · 08/22/23

@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 · 08/23/23

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

Formulas and Functions

Formulas and Functions

Index/Match Formula - No Match to show Blank

Best Answers

IFERROR Function | Smartsheet Learning Center

Answers

IFERROR Function | Smartsheet Learning Center

Help Article Resources

Quick Links

Categories

Trending in Formulas and Functions

Different way to track health

Index Collect Contains Multi Dropdown and Text String

Report Not Showing the Correct Information on Dashboard