or for an ad-free Fetcheveryone experience!

SpreadEveryone: The Fetchland Excel wire

3 lurkers | 101 watchers
Diogenes
Diogenes
Nov 2022
10:34am, 24 Nov 2022
74,523 posts
  •  
  • 0
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
um
Nov 2022
10:41am, 24 Nov 2022
6,749 posts
  •  
  • 0
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
quimby
Nov 2022
10:46am, 24 Nov 2022
5,916 posts
  •  
  • 0
quimby
You could use:

=IF(ISNUMBER(SEARCH(B1,A2)),"Yep","Nope")

ISNUMBER works for text too, IME.
SPR
SPR
Nov 2022
10:47am, 24 Nov 2022
39,003 posts
  •  
  • 0
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
Diogenes
Nov 2022
10:48am, 24 Nov 2022
74,524 posts
  •  
  • 0
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
um
Nov 2022
10:49am, 24 Nov 2022
6,750 posts
  •  
  • 0
um
I played around a bit and (hopefully) corrected it
Try =IF(IFERROR(FIND(A2,B1,1),0)=0,"",1)
SPR
SPR
Nov 2022
10:51am, 24 Nov 2022
39,004 posts
  •  
  • 0
SPR
ISERROR will be neater

=IF(ISERROR(FIND(B1,A2,1)),"",1)
SPR
SPR
Nov 2022
10:53am, 24 Nov 2022
39,005 posts
  •  
  • 0
SPR
ISNUMBER works in a similar way as an error isn't a number.
Diogenes
Diogenes
Nov 2022
10:59am, 24 Nov 2022
74,525 posts
  •  
  • 0
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
Diogenes
Nov 2022
11:05am, 24 Nov 2022
74,526 posts
  •  
  • 0
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.

About This Thread

Maintained by John66
This is bugging me, I'm sure it should be much easier

A1 has 25/6/2010
B1 has 24/6/2013

So t...

Related Threads

  • excel
  • support
  • tech
  • work








Back To Top

Tag A User

To tag a user, start typing their name here:
X

Free training & racing tools for runners, cyclists, swimmers & walkers.

Fetcheveryone lets you analyse your training, find races, plot routes, chat in our forum, get advice, play games - and more! Nothing is behind a paywall, and it'll stay that way thanks to our awesome community!
Get Started
Click here to join 113,933 Fetchies!
Already a Fetchie? Sign in here