VLOOKUP Problem
 

Hi

I have a small problem with a sheet I have created. I use the following
function to display a customer name and address.

=IF($B$6<"",(VLOOKUP($B$6,Customer_File,2,FALSE)) ,"") in cell B8
=IF($B$6<"",(VLOOKUP($B$6,Customer_File,3,FALSE)) ,"") in cell B9
=IF($B$6<"",(VLOOKUP($B$6,Customer_File,4,FALSE)) ,"") in cell B10

etc etc

When an an account number is entered in B6 the customer name and
address details from Customer_File are displayed in the relevant cells.
The problem is that some of the fields in the array are empty and so on
the main sheet a 0 is displayed instead of just en empty cell so I get
something like

Account P007898

Dexian Engineering Ltd
Albion Street
0
0
Lincoln
0
LN16 1GG

Where the 0 is displayed I just want an empty cell. Please advise what
might be wrong. All cells are formatted as text.

Thanks

VC


--
uplink600
------------------------------------------------------------------------
uplink600's Profile: http://www.excelforum.com/member.php...fo&userid=9408
View this thread: http://www.excelforum.com/showthread...hreadid=487957

VLOOKUP Problem
 

ToolsOptionsView, uncheck Zero values

--
Kind regards,

Niek Otten

"uplink600" wrote
in message ...

Hi

I have a small problem with a sheet I have created. I use the following
function to display a customer name and address.

=IF($B$6<"",(VLOOKUP($B$6,Customer_File,2,FALSE)) ,"") in cell B8
=IF($B$6<"",(VLOOKUP($B$6,Customer_File,3,FALSE)) ,"") in cell B9
=IF($B$6<"",(VLOOKUP($B$6,Customer_File,4,FALSE)) ,"") in cell B10

etc etc

When an an account number is entered in B6 the customer name and
address details from Customer_File are displayed in the relevant cells.
The problem is that some of the fields in the array are empty and so on
the main sheet a 0 is displayed instead of just en empty cell so I get
something like

Account P007898

Dexian Engineering Ltd
Albion Street
0
0
Lincoln
0
LN16 1GG

Where the 0 is displayed I just want an empty cell. Please advise what
might be wrong. All cells are formatted as text.

Thanks

VC


--
uplink600
------------------------------------------------------------------------
uplink600's Profile:
http://www.excelforum.com/member.php...fo&userid=9408
View this thread: http://www.excelforum.com/showthread...hreadid=487957

VLOOKUP Problem
 

Hi

You could suppress the zero's by using ToolsOptionsView and untick Zero
values
or try
=IF($B$6="","",IF(VLOOKUP($B$6,Customer_File,2,FAL SE))="","",VLOOKUP($B$6,Customer_File,2,FALSE)))

Regards

Roger Govier


uplink600 wrote:
Hi

I have a small problem with a sheet I have created. I use the following
function to display a customer name and address.

=IF($B$6<"",(VLOOKUP($B$6,Customer_File,2,FALSE)) ,"") in cell B8
=IF($B$6<"",(VLOOKUP($B$6,Customer_File,3,FALSE)) ,"") in cell B9
=IF($B$6<"",(VLOOKUP($B$6,Customer_File,4,FALSE)) ,"") in cell B10

etc etc

When an an account number is entered in B6 the customer name and
address details from Customer_File are displayed in the relevant cells.
The problem is that some of the fields in the array are empty and so on
the main sheet a 0 is displayed instead of just en empty cell so I get
something like

Account P007898

Dexian Engineering Ltd
Albion Street
0
0
Lincoln
0
LN16 1GG

Where the 0 is displayed I just want an empty cell. Please advise what
might be wrong. All cells are formatted as text.

Thanks

VC