• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 354
  • Last Modified:

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.
0
Still Learning
Asked:
Still Learning
  • 6
  • 4
  • 3
2 Solutions
 
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 6
  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now