# Problem with a formula

#### minette

##### Board Regular
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

### Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
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",""))))

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

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

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

Minette

Replies
1
Views
761
Replies
3
Views
113
Replies
4
Views
247
Replies
5
Views
409
Replies
14
Views
2K

1,219,570
Messages
6,149,044
Members
450,853
Latest member
xtiinctt

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back