Wrong output returned during IF function
 

I currently have

=IF(O2="","",(VLOOKUP(O2,risk_impacts!B2:BV95,72,F ALSE)))

Which is returning the correct value of

14

In a different cell I have the following formula based on the value of the
above cell.

=IF(M5<=3,"Green",IF(M5=20,"RED","AMBER"))

Yet the outcome is showing as "RED" when it's clearly "AMBER".

This was in response of conditional formatting not working. As I'm using
2007 I can place 3 separate formatting options on the one cell and yet it was
still not working and was showing always as red.

PLEASE HELP

Wrong output returned during IF function
 

Hi,

Make sure the VLOOKUP's return value, 14, is really a number, not text.

If it is text then change the formula to read:

=IF(--M5<=3,"Green",IF(--M5=20,"RED","AMBER"))

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"jb333" wrote:

I currently have

=IF(O2="","",(VLOOKUP(O2,risk_impacts!B2:BV95,72,F ALSE)))

Which is returning the correct value of

14

In a different cell I have the following formula based on the value of the
above cell.

=IF(M5<=3,"Green",IF(M5=20,"RED","AMBER"))

Yet the outcome is showing as "RED" when it's clearly "AMBER".

This was in response of conditional formatting not working. As I'm using
2007 I can place 3 separate formatting options on the one cell and yet it was
still not working and was showing always as red.

PLEASE HELP

Wrong output returned during IF function
 

Thanks!

"Shane Devenshire" wrote:

Hi,

Make sure the VLOOKUP's return value, 14, is really a number, not text.

If it is text then change the formula to read:

=IF(--M5<=3,"Green",IF(--M5=20,"RED","AMBER"))

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"jb333" wrote:

I currently have

=IF(O2="","",(VLOOKUP(O2,risk_impacts!B2:BV95,72,F ALSE)))

Which is returning the correct value of

14

In a different cell I have the following formula based on the value of the
above cell.

=IF(M5<=3,"Green",IF(M5=20,"RED","AMBER"))

Yet the outcome is showing as "RED" when it's clearly "AMBER".

This was in response of conditional formatting not working. As I'm using
2007 I can place 3 separate formatting options on the one cell and yet it was
still not working and was showing always as red.

PLEASE HELP

Wrong output returned during IF function
 

The 14 is a TEXT number which is not the same as a NUMERIC number. It may be
formatted as TEXT in your lookup table or there might be unseen characters
like spaces making it a text value: <space14

A TEXT value will *always* evaluate to be greater than any numeric number.

So you're getting: TEXT=20 = TRUE.

The best thing to do is to fix this problem at the source which sounds like
the data in your lookup table. Here's one way to convert text numbers into
numeric numbers:

Select an empty cell. It can be any empty cell.
Copy that empty cell: EditCopy
Select the text numbers to be converted
Then do: EditPaste SpecialAddOK

--
Biff
Microsoft Excel MVP


"jb333" wrote in message
...
I currently have

=IF(O2="","",(VLOOKUP(O2,risk_impacts!B2:BV95,72,F ALSE)))

Which is returning the correct value of

14

In a different cell I have the following formula based on the value of the
above cell.

=IF(M5<=3,"Green",IF(M5=20,"RED","AMBER"))

Yet the outcome is showing as "RED" when it's clearly "AMBER".

This was in response of conditional formatting not working. As I'm using
2007 I can place 3 separate formatting options on the one cell and yet it
was
still not working and was showing always as red.

PLEASE HELP