Problem with a formula

[minette]

Hi all,

I have the following formulas and text in cells B1 to E1

in (B1) NR12 7ER
in (C1) =IF(ISERROR(E2),"0",IF(E2>=1,E2,"OK"))
in (D1) =IF(C2="0","1.00",IF(C2=1,"1.20",IF(C2=2,"Refer EA",IF(C2=3,"Decline",""))))
in (E1) =VLOOKUP(A1,'Flood'!A:B,2,FALSE)

The description of these cells are as follows:-
in (B1) the user type in a postcode
in (C1) checking cell E1 and put in criteria based on whether an error is returned.
in (D1) this formula allocates text based on the value in cell C1
in (E1) this cell is looking at sheet "Flood" to lookup the number against the postcode specified in cell B1.

The answers returned are as follows:
in (C1) 3
in (D1) (empty)
in (E1) 3

However, D1 should be "Decline" and not be empty.

I wonder if someone has any ideas of why this formula does not work. I have a hunch that it has something to do with referring to formulas and not text etc, etc, but I need an expert to help me get to the bottom of this one.

Thanks to everyone who's having a go.

Minette

 

[just_jon]

Remove the quotes from your numerics -

in (C1) =IF(ISERROR(E2),0,IF(E2>=1,E2,"OK"))
in (D1) =IF(C2=0,1,IF(C2=1,1.20,IF(C2=2,"Refer EA",IF(C2=3,"Decline",""))))

 

[minette]

Thanks for your very quick response - unfortunately, it did not work. Do you have any other ideas?

 

[barry houdini]

Seems like Jon's suggestion would be sensible but it looks like the problem is that your VLOOKUP returns a text value, so either change the "numbers" in Flood!B:B to real numbers by selecting that column and using

Data > Text to columns > Finish

...or change your VLOOKUP formula to

=VLOOKUP(A1,'Flood'!A:B,2,FALSE)+0

 

[minette]

Hi Barry -I changed the vlookup formula as you suggested, and it WORKS great. Thanks very much for your help.

Minette