We help IT Professionals succeed at work.

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

Still Learning
on
Medium Priority
371 Views
Last Modified: 2012-05-11
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.
Comment
Watch Question

Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
CERTIFIED EXPERT
Top Expert 2011
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
CERTIFIED EXPERT
Top Expert 2011

Commented:
Hi Sid, sorry did not see your input before posting.
That's ok JimyZ :)

Sid

Author

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
CERTIFIED EXPERT
Top Expert 2011

Commented:
Remove the spaces from cell C11 and any other cell, otherwise you do not get match.
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

Author

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
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
CERTIFIED EXPERT
Top Expert 2011

Commented:
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".
Here is the updated Sheet

Sid
match-test.xlsx

Author

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!
You are welcome :)

Sid
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.