IF VALUE

Art Schneiderheinze · 05/18/20

Is there an easy way to do this?

Currently, I look for a match between a cell in Sheet 1 and cells in Sheet 2. If it finds a match, it uses the value in Sheet 2, otherwise it shows "No". This works fine.

However, I'd like to add another IF so that if the value in Sheet 2 that results from the match is "Canceled", I want it to display "No" as well, not "Canceled". The only way I can think to do this is to evaluate the match twice.

IF (result of match is "Canceled"), then "No", otherwise do the match.

"result of match" and "do the match" is the same long formula. I'd prefer to not have to repeat it (or create a second column), if possible.

Here is an example

CURRENT

=IFERROR(INDEX formula, "No")

WANT SOMETHING LIKE THIS

=IF RESULT IS "Canceled", "No", otherwise IFERROR(INDEX formula, "No") without having to do something like this:

=IF IFERROR(INDEX formula, "No") = "Canceled", "No", otherwise IFERROR(INDEX formula, "No").

L_123 · 05/18/20

Easiest way is to use a helper cell with the index formula then reference that back.The only other thing I can think of is using an iferror to somehow force a bad return, then using the bad return to get you the correct response.

What is causing your index formula to be so complex? In my experience index(match()) should generally be straightforward, the more complicated ones can usually be shortened with collect()

Art Schneiderheinze · 05/18/20

It's not itself complex, I am just trying to avoid having to repeat it.

IF (result is blue), "---", otherwise INDEX(MATCH)

Yes, could easily do this in a Helper cell, was just seeing if there was a way to avoid having to repeat the entire INDEX(MATCH) formula to display something other than the result if the result is a specific value.

IF INDEX(MATCH) is "Canceled", display "---", not Canceled. Otherwise, display the status---e.g., In Progress, Completed.

L_123 · 05/18/20

HA! I found a way. You can use the SUBSTITUTE formula.

=substitute(index(match()),"Canceled","---")

@Paul Newcome I think you'd like this one. I never thought of sticking an index match inside a substitute before.

Paul Newcome · 05/18/20

@L@123 Nice! I have used an INDEX/MATCH inside of a SUBSTITUTE before, but not quite like this... I wouldn't have thought of doing it this way. I probably would have just ended up using

=IF(INDEX(MATCH()) = "Cancelled", "---", INDEX(MATCH()))

Using the SUBSTITUTE function is pretty slick, and I feel like this could be incorporated into a lot of other solutions. I like it for sure. I am going to "favorite" this post for future reference.

@Andrée Starå, @Genevieve P, and @Mike Wilday... Y'all might be interested in the above solution as well...

Art Schneiderheinze · 05/18/20

That's it! Worked like a charm. Much thanks!

Mike Wilday · 05/18/20

@L@123 Great solution. @Paul Newcome That is a solution I would have tried on Google Sheets but never would have using substitute crossed my mind here. It makes such perfect sense when you think about it. haha.

Paul Newcome · 05/19/20

@Mike Wilday I actually had to read it a couple of times before it clicked how exactly it was working. Haha. The more I look at it the more I think "Brilliant!"

L_123 · 05/19/20

@Paul Newcome @Mike Wilday

Thanks guys. I'm kind of excited about this formula, the way it is used in this solution is probably one of the simpler applications, but after some testing i've realized we can output different datatypes with it as well (such as booleans or dates). I haven't used substitute much historically, but that is definitely going to change.