for an ad-free Fetcheveryone experience!

SpreadEveryone: The Fetchland Excel wire

102 watchers

Diogenes Nov 2022 10:34am, 24 Nov 2022 74,523 posts	Quote Pin Diogenes Thanks Um, that works great for cells where there is a match but returns an error for those where there isn't, which is most of them. How do I get it to put a "0" or preferably leave it blank when there's no match?
um Nov 2022 10:41am, 24 Nov 2022 6,749 posts	Quote Pin um I thought that's what the formula was doing, ie if an error, enter "" (null), otherwise 1. Did you copy it fully? If so, what error is it giving?
quimby Nov 2022 10:46am, 24 Nov 2022 5,916 posts	Quote Pin quimby You could use: =IF(ISNUMBER(SEARCH(B1,A2)),"Yep","Nope") ISNUMBER works for text too, IME.
SPR Nov 2022 10:47am, 24 Nov 2022 39,003 posts	Quote Pin SPR I think you need ISERROR if you're trying to encase it in an IF. IFERROR returns the value in your second portion if your formula in the first portion is an error, but it doesn't produce a true/ false which is what IF needs. That's what ISERROR does.
Diogenes Nov 2022 10:48am, 24 Nov 2022 74,524 posts	Quote Pin Diogenes That's what I expected, Um, looking at it. it gives me a #VALUE! error I'll try Quimpy's suggestions too. Thank you both
um Nov 2022 10:49am, 24 Nov 2022 6,750 posts	Quote Pin um I played around a bit and (hopefully) corrected it Try =IF(IFERROR(FIND(A2,B1,1),0)=0,"",1)
SPR Nov 2022 10:51am, 24 Nov 2022 39,004 posts	Quote Pin SPR ISERROR will be neater =IF(ISERROR(FIND(B1,A2,1)),"",1)
SPR Nov 2022 10:53am, 24 Nov 2022 39,005 posts	Quote Pin SPR ISNUMBER works in a similar way as an error isn't a number.
Diogenes Nov 2022 10:59am, 24 Nov 2022 74,525 posts	Quote Pin Diogenes Thank you, my one amendment is to =IF(ISERROR(FIND($D$1,B2,1)),"",1) so that I can copy down the columns, otherwise the references get messed up
Diogenes Nov 2022 11:05am, 24 Nov 2022 74,526 posts	Quote Pin Diogenes Hmm, now I have to find of eliminating or flagging partial matches, e.g. 1a also returns a match for 11a That might have to be a manual check as there are only a few columns where that might happen.