Conditional formatting does not work with MATCH / VLOOKUP

  • Thread starter Thread starter AA Arens
  • Start date Start date
A

AA Arens

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
 
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 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
Click to expand...

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
 
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.
 
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.
Click to expand...

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
 
Back
Top