Thanks:  0
Likes:  0

# Thread: Similar once again to blank formula fields

1. 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?

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

3. 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!

4. 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?

5. 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!

6. 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?

7. 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

8. 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.

9. 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!

10. 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.

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•