//some 3rd party need geo

Lookup function in Excel

  Craig.m 20:46 11 Nov 04
Locked

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)

Thanks

Craig

  VoG II 22:10 11 Nov 04

Try

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

  Bramblerose 22:22 11 Nov 04

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

Craig

  VoG II 22:26 11 Nov 04

=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 04

#VALUE! was the response in that cell.

Cheers

Craig

  cherria 11:30 15 Nov 04

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 04

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,

Regards,

Simsy

  Craig.m 16:25 17 Nov 04

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

Thanks

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

What is Google Allo? What is Google Duo? Google Allo UK release date rumours and features: Google…

1995-2015: How technology has changed the world in 20 years

These clever designs help visualise a complex intelligence tool

iOS 10 troubleshooting tips: Simple fixes for the most common iOS 10 problems, from network…