Wrong output returned during IF function

  • Thread starter Thread starter jb333
  • Start date Start date
J

jb333

I currently have

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

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
 
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"))
 
Thanks!

Shane Devenshire said:
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
Click to expand...
 
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: <space>14

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: Edit>Copy
Select the text numbers to be converted
Then do: Edit>Paste Special>Add>OK
 
Back
Top