Hi Tom,

My company has decided to use a standard form for the sales team when we place our customers orders. This would be great if the person doing it knew more than I did about Excel! I am using Excel 2010.

Anyway, they gave me a form with tons of fields that have no drop down lists. Not customers, parts...nothing. Now, I can make drop down lists, that I get. What I would like to do is enter my customers Company name and have info like address, phone, email, account# pre-fill. I see how to link lists with Data Validation but I do not think that is what I want because I need the company info to be identifiable. I can't just have a list of emails, phone numbers and names.

So, to sum up, I want fields to pre-fill when I enter a company name. Please talk to me as though you are writing EXCEL for DUMMIES. I appreciate all your help and I can send you the shoddy template if you need me to.

Best Regards,



If you had a database/list (multiple rows and columns) with Company Name in the first column, then  you could use the vlookup formula.

Assume the list is like this on a sheet Named Data

A1: Company name
B1: Street address
C1: City
D1: State
E1: Zip
F1: phone
G1: email
H1: account#

A2: Smith and Smyth, Inc
B2: 111 West East St
C2: Indianapolis
D2: IN
E2: 21213
F2: (123)456-7890
G2: abc@smithsmythinc.com
H2: A12345

then if on another sheet (your template in the same workbook) I had in F20
F20:  =if(B2<>"",Vlookup(B2,Data!A:H,7,False),"")

then the company name would be placed in B2 and this formula would produce the companies email address in F20 because in the lookup range Data!A:H, column G is the 7th column in that range (3rd argument of Vlookup - specifies which value to return from the matching row)  

The 4th Argument, False, tells vlookup to find an exact match in column A for the value (company name) in cell B2.  

If you need more explanation or an example send an email to twogilvy@msn.com

Tom Ogilvy  
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here


All Answers

Answers by Expert:

Ask Experts


Tom Ogilvy


Selected as an Excel MVP by Microsoft since 1999. Answering Excel questions in Allexperts since its inception in 2001. Able to answer questions on almost all aspects of Excel's internal capabilities. If seeking a VBA solution, please specify that in your question itself so I give you the answer you want. [Excel has weak protection - if you are distributing an application, I don't answer questions on how to protect your project from your users.]


Extensive experience.

Master of Science (MS) degree Operations Research (ORSA)

Awards and Honors
Microsoft MVP in Excel.

©2016 About.com. All rights reserved.