Need to be able to type customer name in one field and the account number populates in another

Copper Contributor

Hi I have a workbook with multiple worksheets. The first sheet is my master client list.  I add to this list when I get new customers.  Every month I document my clients and revenue. I have to go back to the master list to get the account number.  I know there is a way to have this information tied together, so when I type in the customer name or set up a dropdown list, the account number cell is filled in correctly. Thanks so much for your help.

Test Example.xlsx

 

 

7 Replies

@jackiebiggs 

 

I assume those are real names of real clients. You should take the images off your post (I can't do it for you). 

 

From what you've said, it sounds as if one of the various functions (for looking up values related to account numbers or names) would be the answer to your implied question. Here's a link that explains the VLOOKUP function, one of the most widely used in Excel. On that page, you'll also find links to other related functions like INDEX and MATCH.  If you need additional help in implementing a solution, please come back with a mockup (no real names please) of your workbook, and post that mockup spreadsheet, not just an image, on OneDrive or GoogleDrive, and post a link here that grants edit access to the sheet.

 I've added the link to a test workbook. Please let me know what you think. I took a look at the VLOOKUP before I posted, but wasn't sure exactly what to do. Thanks so much.

@mathetes 

I've updated this with the attachment.  Please let me know how I can do this.  I looked at the VLOOKUP, but it appeared that this was to search for data already in the workbook. I want to add to my monthly sales spreadsheet, and have the data pull from the first tab, the master customer list.

@rangelsammon 

I looked at the XLOOKUP, but it appeared that this was for searching for data already in the spreadsheet (at least the examples I saw).  I want to put the info in the master customer list, and then have it fill in the account number when I type in the client name.

@jackiebiggs 

 

I've attached your example with a formula in the column for Customer ID that looks up that number, based on the name. I used INDEX and MATCH because the way you are doing this would be considered by many IT professionals to be "backwards," in that you are using the name to find the ID. The ID is unique; names may or may not be unique; that's why it's "backwards" from the normal way of doing it.

 

But it gives you the idea of how that function works.

 

I took a look at the VLOOKUP before I posted, but wasn't sure exactly what to do.

 

What you should do in cases where you're being introduced to a new function is follow the examples and build your own. You'll learn better that way, even though it may take a little trial and error.

 

To save you the trouble this time, I've inserted a sheet that uses VLOOKUP as well as the one you've already created. In the process, using Customer ID as the operative argument, I realized that some of your IDs were entered as "text" and others as "numbers," which could mess you up. I'd recommend having IDs that can't be that ambiguous--and consistently using text is the more reliable. So add a meaningful letter or two at the start. For example, I made the IDs "CC1001", "CC1002" and so forth.

@jackiebiggs 

 

I looked at the XLOOKUP, but it appeared that this was for searching for data already in the spreadsheet (at least the examples I saw). 

 

Jackie: you put your finger on the issue....those were examples. Only examples. They were not the only way to do XLOOKUP; XLOOKUP can work between tabs in the same workbook, as you have it. All you had to do was try it in your situation. But see the sample I sent back to you, two different ways to do it, neither using XLOOKUP, but both working as well. Within Excel, you'll find that there are almost always several different ways to get from Point A to Point B. XLOOKUP,, VLOOKUP, INDEX and MATCH....there are more still to retrieve data from one table and place it some place else in the same workbook, whether or not on the same sheet.

 

Give yourself permission to experiment.