check to see if a cell's value is equal to any cell's value in a given range

I hope this is as easy as I think it will be for somebody out there...

If you have a named range of cell's with numbers, and you want to check if a cell's value is equal to any cell's value in that named range.

Ideally the solution would be simple, without VBA, so it can be used in conditional formatting, inside other formulas, etc.
Still LearningAsked:
Who is Participating?
 
SiddharthRoutCommented:
This will check for the existence of 1 in that range

=COUNTIF(NamedRange,1)

Sid
0
 
jimyXCommented:
You can do that by using CountIf.
Let's assume you have the range B1 to B12 and you want to check for the value in A1 then your formula will be:

=IF(COUNTIF(B1:B12,A1)>0,"Found","Not found")
0
 
jimyXCommented:
Hi Sid, sorry did not see your input before posting.
0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

 
SiddharthRoutCommented:
That's ok JimyZ :)

Sid
0
 
Still LearningAuthor Commented:
Good idea.  I am trying it, and as you can see by the highlighted fields that should say "yes" I must have done something wrong.

Also, it would be nice if a match resulted in the actual value that was matched, but I will take what I can get and repost a different question if neccessary.

Thank you for your help!!!
match-test.xlsx
0
 
jimyXCommented:
Remove the spaces from cell C11 and any other cell, otherwise you do not get match.
0
 
SiddharthRoutCommented:
Don't have access to 2007 at the moment so won't be able to see the formulas that you have put but yes, like JimyX suggested, spaces in search Criteria or the Range value will not give you right result. :)

Sid
0
 
Still LearningAuthor Commented:
I have reloaded the little example file.  I am not sure what you are referring to as spaced in C11.  Every cell that should have returned a "yes" is now highlighted, and all formatting has been centered and changed to a number.  I am still doing something simply wrong, aren't I?!
match-test.xlsx
0
 
SiddharthRoutCommented:
Just Rebooted. Now I have access to Excel 2007

Two things

The formula for example in cell L8 should be =IF(COUNTIF(Match1,D8)>0,"yes","no")

and also there is space before 22 in cell D8

Amend that and you will get the result.

Sid
0
 
jimyXCommented:
The first cell that did not give you match E7, the content should be only "18" but you have space at the beginning " 18" so you got "No".
0
 
SiddharthRoutCommented:
Here is the updated Sheet

Sid
match-test.xlsx
0
 
Still LearningAuthor Commented:
Thanks to both of you... I know that entering answers on these can sometimes be timing.  You were both on the same track.  Those little spaces had me fooled, and you both kept at me about it... thank you for staying with me!
0
 
SiddharthRoutCommented:
You are welcome :)

Sid
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.