Conditional Formating - not in table

[GregR]

I have a worksheet that has many columns, one of which is a model
number. I have conditional formated this column, so that if the model
number exists in a table(Supply) on another sheet, it colors the cell.
I am using the following formula in CF to get that result:

=$C4=vlookup($C4,Supply,1,False) and it works as designed. What I want
is the opposite effect, if the item is not found in the Supply table, I
want the CF to apply. I have tried:

=$C4<>vlookup($C4,Supply,1,False)as well as many other combos to no
avail. What is the formula. TIA

Greg

 

[Guest]

Try =$C4=isna(vlookup($C4,Supply,1,False))

Duke

 

[Bob Phillips]

Greg,

Try

=NOT(ISNUMBER(MATCH($C4,Supply,0)))

you can also simplify yours to

=ISNUMBER(MATCH($C4,Supply,0))


--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Guest]

Bob -

You know - I gotta stop putting my $.02 in, because you and other MVPs keep
offering up suggestions & solutions that put mine to shame.

 

[Bob Phillips]

Don't stop, there's always the gem that you spot that would then be missed.

Anyway, it helps show the OP that there is more than one way, and that way
they should learn more.

Best Regards

Bob

 

[GregR]

Bob, as usual, thanks for you tremendous help and Duke, keep them coming.
Bob, just one question, if my model numbers are actually text values, would
I change the isnumber to istext. TIA

Greg

 

[Bob Phillips]

No, because the ISNUMBER applies to the result of the MATCH, which will
return an index (number) if found, and #N/A if not. ISNUMBER is used to get
a True/False value.

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[GregR]

Bob thanks

Greg

No, because the ISNUMBER applies to the result of the MATCH, which will
return an index (number) if found, and #N/A if not. ISNUMBER is used to
get
a True/False value.

--

HTH

RP
(remove nothere from the email address if mailing direct)