## Auto-entry of customer info on invoice

308 Views 3 Replies Latest reply: Jan 16, 2013 4:39 AM by Peter Thomson
Level 2 (345 points)
Currently Being Moderated
Jan 15, 2013 10:37 PM

I have a sheet that is a customer database, and includes a unique number for each customer, along with their name, address and phone number. In seperate sheets (in the same file), I would like to enter the customer's unique number and have their name, address and phone number populate the cells on invoices and receipts. I am new to this type of formula, so would like to ask if there is anyone who may be able to help me. Thank you in advance, and apologies if this has been asked elsewhere.

• Level 7 (28,740 points)
Currently Being Moderated
Jan 16, 2013 12:05 AM (in response to Peter Thomson)

Hi Peter,

This is essentially a LOOKUP function.

The table containing the customer number, name, address and phone number is used as a lookup table for a formula like the one below:

Table name: Lookup

Column A: Customer number

Column B: Name

Column E: City  ST  Code

Column F: Phone

On the invoice or receipt table, Customer number entered in cell A1

Formula to return Customer Name: =VLOOKUP(A1,Lookup::\$A:\$F,2,FALSE)

Formula to return Phone Number:   =VLOOKUP(A1,Lookup::\$A:\$F,6,FALSE)

Details on VLOOKUP (and all other functions supported in Numbers) may be found in the iWork Formulas and Functions User Guide. The guide may be downloaded through the Help menu in Numbers '09.

Regards,

Barry

LOOKOUT looks for a "close match" to the search term. If the customer number entered is not in the database, LOOKUP will return information tied to the "largest value less than or equal to" the search term.

VLOOKOUT can do this, but it can also demand an exact match of the search term. If the customer number is not in the database, VLOOKUP (using the FALSE argument shown above) will return a 'not found' error.

B

#### More Like This

• Retrieving data ...

#### Legend

• This solved my question - 10 points
• This helped me - 5 points
This site contains user submitted content, comments and opinions and is for informational purposes only. Apple disclaims any and all liability for the acts, omissions and conduct of any third parties in connection with or related to your use of the site. All postings and use of the content on this site are subject to the Apple Support Communities Terms of Use.