Similar once again to blank formula fields

danielle

New Member
Joined
Mar 16, 2002
Messages
10
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! :biggrin:
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
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!
 
Upvote 0
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?
 
Upvote 0
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!
:biggrin:
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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!
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,517
Messages
6,114,085
Members
448,548
Latest member
harryls

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top