Help with VLOOKUP

[OdAwG]

Hello All Excel Guru's:

I have another quick question that I need assistance with that pertains or
deals with conditional format.

I have a list of names in one column and I need to search another list
(range) to see if that name DOES NOT EXIST in that list or range. If it
DOES NOT Exist, then to change the color of the cell. Listed below is the
my example:

A B C D E F
1 Names Semi
2 Hulk Hulk Storm Spidey Batman
3 Storm Storm Batman Ironman Spidey
4 Spidey IronMan Phoenix Batman Hulk
5 BatMan Spidey Batman Storm Hulk
6 SuperMan IronMan Hulk Spidey Phoenix
7 IronMan IronMan BatMan Hulk Storm
8 CatWm
9 Phoenix
10 Kitty

A2:A10 is a range called Names
C2:F7 is a range called SEMI

In the above example, the following three (3) names should be colored out
(CatWm, Superman, and Kitty) because those names DO NOT APPEAR in the SEMI
range.

=vlookup(A2,Semi,1,False) but I keep getting #N/A <-- I do not think my
formula is correct and also, do I put this in the conditional format so that
I can color out the cell.

I'm not very strong in using VLOOKUP, any and all help is greatly
appreciated.

Argus

 

[Dave Peterson]

How about:

formula is:
=countif(semi,a1)=0

=vlookup() only looks for a match in the first column of the lookup range.

 

[OdAwG]

That doesn't seem to work for all, when I copied your formula down the list,
it show FALSE for alot of names that appear in the SEMI range

 

[T. Valko]

Don't copy the formula down the list!

Select the range A2:A7. A2 should be the active cell. The active cell is
clear while the rest of the cells in the selected range have a "blueish"
color.

Goto the menu Format>Conditional Formatting
Formula Is: =COUNTIF(semi,A2)=0
Click the Format button
Select the style(s) desired
OK out

As long as the defined name "semi" has absolute references the CF formula
should work. (unless there might be unseen characters like leading/trailing
spaces in some cells)

Biff

That doesn't seem to work for all, when I copied your formula down the
list, it show FALSE for alot of names that appear in the SEMI range

 

[OdAwG]

Hey Mr. "D"

I read it wrong, your formula does work correctly, for the names that it
found it shows False, and the ones that are not in the list shows true. I
had a brain fart there and I apologize.

Thanks for the help...

 

[OdAwG]

Hey Sir,

Forgot to ask, how do I use this now with conditional format to change the
cell color for those names that equal True

Argus

 

[Dave Peterson]

Check the response from Biff (aka T. Valko). He gives instructions to you.

But...

If you're going to do anything with this list, you may want to insert an
adjacent cell and use that formula in that column.

Then you can apply Data|Filter|autofilter and see the ones you want (either true
or false).

In fact, you may want to do both--use format|Conditional formatting and the
helper column.