How do i check if something non numeric is in a range & return'Y'

[Guest]

I've got a massive list of phones, some of which are colour and some are not eg
s60X
8810
530g
7210
6210

I have another list which tells me which phones are colour
Phone Colour
7210 Y
6210 Y
g43 y

How do i find out without doing it manually which phones in my first list
are colour

AM gutted that i can't work this out

 

[Guest]

amung the ways would be conditional formatting
select the first data set
<format><Conditional Format>
Formula is
Upper(vlookup(Current cell,second range,2,false))="Y"
set the format as you want.
note I often have to go backinto conditonal format to get rid of unwanted
quote marks and verify relative absolute cell referencing is correct.

 

[CLR]

Assume your first list is in column A and your second in columns J and K
in B1 put this formula and copy down........

=IF(ISNA(VLOOKUP(A1,J:K,2,FALSE)),"",VLOOKUP(A1,J:K,2,FALSE))

Vaya con Dios,
Chuck, CABGx3

 

[Guest]

That, my friend, was the bollocks!!

Thank you so much
B