Similar once again to blank formula fields

[danielle]

e.g. A1 - data entry, B1 - VLOOKUP function, C1 - VLOOKUP function, where B1 and C1 look up A1.

When A1 is blank, B1 and C1 display #VALUE (or something to that effect). When A1 is blank I want them to be blank.

I put in an IF statement - IF(,(VLOOKUP(A1,'Table!A9:B20',2,0),"")

What do I put in as the first variable of the IF statement. Or are there some other ideas?

Please help!

[Steve Hartman]

What do I put in as the first variable of the IF statement. Or are there some other ideas?

A1<>""

[Yogi Anand]

Hi Danielle:
If your lookup value is in cell A1, your lookup range is A9:B20, you are looking up the value in column 2, you want an exact match ... and if A1 is blank you want the looked up value (say in cell B1) to be also blank, then your formula in cell B1 should be:

=if(A1="","",vlookup(A1,$A9:$B20,2,0))

I hope this is what you want ... please post if it works for you, otherwise explain a little further what you are trying to acomplish!

[danielle]

The above doesn't work. Can anyone else help?

The problem is explained in the first post but if you've missed it:

A1 - Normal data, B1 - VLOOKUP function, C1 - VLOOKUP function, where B1 & C1 look up A1.

When A1 is blank, B1 and C1 show #VALUE. What I want is an IF statement so that when A1 is blank, so are B1 and C1.

Can anyone help?

[y2k]

Yogi answered your query above. That should work. What you need to do is put =if(a1="","",VLOOKUP(A1,'Table!A9:B20',2,0)) in cell B1 & C1. This way if A1 is blank, it doesn't even look at the VLOOKUP function. I hope this works... be sure to let us know!

[Aladin Akyurek]

On 2002-03-19 05:12, danielle wrote:
The above doesn't work. Can anyone else help?

The problem is explained in the first post but if you've missed it:

A1 - Normal data, B1 - VLOOKUP function, C1 - VLOOKUP function, where B1 & C1 look up A1.

When A1 is blank, B1 and C1 show #VALUE. What I want is an IF statement so that when A1 is blank, so are B1 and C1.

Can anyone help?

What do you get if you try the following:

=LEN(A1)

while VLOOKUP ends up in a #VALUE! error?

[danielle]

When I typed in what everyone suggested it didn't work so I tried to do it through the Function Wizard and for some weird reason it worked. This is the statement I ended up with.

=IF(C21>"",VLOOKUP(C21,'Lookup Table'!A10:B24,2,0),"")

Go figure!!

Thanks for everyone's help

[Aladin Akyurek]

On 2002-03-19 06:54, danielle wrote:
When I typed in what everyone suggested it didn't work so I tried to do it through the Function Wizard and for some weird reason it worked. This is the statement I ended up with.

=IF(C21>"",VLOOKUP(C21,'Lookup Table'!A10:B24,2,0),"")

Go figure!!

Thanks for everyone's help

Somehow I don't like what that wizard proposes.

The formula Anand suggested should work.

By the way, I asked applying LEN to see whether you had a space instead of a blank in the cell from where you pick up the lookup value.

Aladin

[danielle]

I didn't try LEN because I don't know what it is and by that time my IF statement worked.

By function wizard I mean:
Insert --> Function --> Logical, IF --> OK --> then I basically filled in the blanks. I don't understand why when I type in the exact same formula it doesn't work!

[Aladin Akyurek]

On 2002-03-19 07:23, danielle wrote:
I didn't try LEN because I don't know what it is and by that time my IF statement worked.

By function wizard I mean:
Insert --> Function --> Logical, IF --> OK --> then I basically filled in the blanks. I don't understand why when I type in the exact same formula it doesn't work!

If the word CAR is in B1, =LEN(B1) in C1 will show 3. If B1 is blank, C1 will show 0.

Aladin