Conditional formatting does not work with MATCH / VLOOKUP

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

AA Arens - 26 Dec 2006 11:11 GMT

I do have two sheets and use conditional formatting.

One sheet works fine.

When I get a value on that sheet the other sheet with MATCH and
VLOOKUP, and use the same conditional formatting on that sheet, CF does
not work at all.

Bart
Excel 2003

Reply to this Message

AA Arens - 26 Dec 2006 13:54 GMT

I have three conditions, in the mode "Cell Value". When the value match
the 3rd condition, it is indicated according tio that condition. All
other value who should match the 1st and 2nd conditions are not
indicated to their respective conditions.

Bart

> I do have two sheets and use conditional formatting.
>
[quoted text clipped - 6 lines]
> Bart
> Excel 2003

Reply to this Message

Ron Rosenfeld - 26 Dec 2006 17:52 GMT

>I have three conditions, in the mode "Cell Value". When the value match
>the 3rd condition, it is indicated according tio that condition. All
>other value who should match the 1st and 2nd conditions are not
>indicated to their respective conditions.
>
>Bart

Given the limited information you supply, diagnosis and treatment is difficult.

Most likely your formulas are not doing what you expect; or your data is not
what your formulas expect (rounding can do this, for example).

If that does not point you in the desired direction, you will need to supply
more information.
--ron

Reply to this Message

AA Arens - 27 Dec 2006 01:35 GMT

This is the formulah in the cells:
All values looked up are whole values.

=IF(ISNUMBER(MATCH(C7,CTI!$E$7:$E$206,0)),VLOOKUP(C7,CTI!$E$7:$O$206,11,0),"")&
IF(ISNUMBER(MATCH(C7,'Batavia Air'!$E$7:$E$206,0)),VLOOKUP(C7,'Batavia
Air'!$E$7:$O$206,11,0),"")&
IF(ISNUMBER(MATCH(C7,'Multi
Structure'!$E$7:$E$206,0)),VLOOKUP(C7,'Multi
Structure'!$E$7:$O$206,11,0),"")&
IF(ISNUMBER(MATCH(C7,'PEC Tech'!$E$7:$E$206,0)),VLOOKUP(C7,'PEC
Tech'!$E$7:$O$206,11,0),"")&
IF(ISNUMBER(MATCH(C7,Adhimix!$E$7:$E$206,0)),VLOOKUP(C7,Adhimix!$E$7:$O$206,11,0),"")&
IF(ISNUMBER(MATCH(C7,JCB!$E$7:$E$206,0)),VLOOKUP(C7,JCB!$E$7:$O$206,11,0),"")

Bart

The conditions are:

1. value 28-14 (green text)
2. <= 14 (orange text)
3. if value "Expired" (bold red)

The latter is formatted correctly, not the formats 1 and two. While in
the other sheets these cvalues are formatted well.

> >I have three conditions, in the mode "Cell Value". When the value match
> >the 3rd condition, it is indicated according tio that condition. All
[quoted text clipped - 9 lines]
> more information.
> --ron

Reply to this Message

Ron Rosenfeld - 27 Dec 2006 02:08 GMT

>This is the formulah in the cells:
>All values looked up are whole values.
[quoted text clipped - 20 lines]
>The latter is formatted correctly, not the formats 1 and two. While in
>the other sheets these cvalues are formatted well.

It looks to me as if your formula is returning TEXT (a string of numbers), and
the conditional test for values that you are using requires a NUMERIC value.
Concatenation is a string function and returns a string.

From HELP:

& (ampersand): Connects, or concatenates, two values to produce one
continuous text value ("North"&"wind")

The fact that you are concatenating numeric values does not somehow change them
from text to numbers.

Depending on your requirements, you could either test for strings of numbers in
the conditional formatting equations; or coerce the result to be numeric with a
double unary:

=--(your_long_formula)

--ron

Reply to this Message

Rate this thread: