Lookup function in Excel

  Craig.m 20:46 11 Nov 2004

I am using a lookup table which contains addressing and company information in cells across the page, all starting with a unique customer number.

On another page I am using the first field to input a customer number and then the lookup function returns the next few fields to insert customer information. The whole line then gets input into a mail merge with word to create invoices etc.
Using the following:

=IF($A2="","",LOOKUP($A2,'Lookup Data'!$A$2:$A$10,'Lookup Data'!I$2:I$10))

I find that a blank gives no returns (as planned) but if there is data in some of the fields but one or two are blank (as in different numbers of lines in addresses) I get a zero returned from the lookup function. This messes up the mail merge, doesn't look too good having zeros in the address line.

Any ideas of how to get the resultant not to put a 0 in when it is a blank in the lookup table?

I suppose I could have tried to do this in a database but I am pretty lousy with databases (not so good with Excel either it now appears)



  VoG II 22:10 11 Nov 2004


=IF($A2="","",LOOKUP($A2,'Lookup Data'!$A$2:$A$10,'Lookup Data'!I$2:I$10, False))

  Bramblerose 22:22 11 Nov 2004

Tried this but came back with an error of too many arguements.


  VoG II 22:26 11 Nov 2004

=IF($A2="","",VLOOKUP($A2,'Lookup Data'!$A$2:$A$10,'Lookup Data'!I$2:I$10, False))

It is better to use VLOOKUP or HLOOKUP as they allow the fourth parameter.

  Bramblerose 22:34 11 Nov 2004

#VALUE! was the response in that cell.



  cherria 11:30 15 Nov 2004

I think this will sort you out

I'm assuming here that you have a lookup table that stretches from A2 to I10

and that in the formula below, you want to return the value from the 3rd column in that table.

=IF($A2="","",IF(VLOOKUP($A2,'Lookup Data'!$A$2:$I$10,3,FALSE)="","",VLOOKUP($A2,'Lookup Data'!$A$2:$I$10,3,FALSE)))

For each column, you need to change the number from 3 to 4 to 5 etc. to get the right column of information.

  Simsy 12:01 15 Nov 2004

it tells Excel to display nothing, (i.e. a blank cell), when the result of a calculation is zero.

Tools>Options>View and UNtick the "Zero Values" option.

This should certainly make it lookcorrect in Excel, but I'm not sure what will be carries over to a mail merge; it might still carry the value over, i.e. "0"

Good Luck,



  Craig.m 16:25 17 Nov 2004

Will give those ideas a try and see if it resolves the problem


This thread is now locked and can not be replied to.

Elsewhere on IDG sites

HTC U12 Plus review: Hands-on

Best Android emulators for Mac

TV & Streaming : comment regarder Roland Garros ?