Formula?

[MargotKloeck]

Hello, In my Excel document, I use names in column C and a long number in D (which is a special code for every name and belongs to the name). In the same document I sometimes have the same names in C, so instead of writing the same number again, I would like to know if there is a possibility to put the corresponding code in D with a formula or something when I write the name in C. And if it's possible, can you tell me how I need to do this, please? Because I'm not an expert :)

Thank you so much!!

[#2]

You can use Drop down List for Column C or D
First Need to create list of Name or Code and select the cells that you want to display
On the ribbon, click DATA > Data Validation.
In the dialog, set Allow to List.
Click in Source, Give range of Name or Code
Click OK.
for getting drop down list of Names or Codes
Next step, use Index match for getting respective code or Name
Incase you provide demo data, it would have been better to give clear idea.

[#3]

Thank you for answering! Does this mean that I have to create a list with all the names that I will see a second time in the document because there are more than a thousand names and I don't know in the beginning when making the list which one I will see a second time.

[#4]

And how do I make the code or name in Source for getting the drop down list? Do you know this by any chance?

[#5 · Accepted solution]

@MargotKloeck Perhaps you can use the method demonstrated in the attached file.

Sheet1 contains a list of names in A and a lookup formula in B2 copied down a bit. The formula expands its lookup range as you copy it down. That's your starting position as if you would start from scratch.

I copied that list to Sheet2 to demonstrate the next step.

Name01 is new so the lookup formula doesn't find it in the rows above. Hence a "-". Now, just over-write the formula with the number for Name01. Do that for every name. The next time you enter Name01 in A, the formula in B will pick it up from the rows above it, and so on.

Just make sure that you always have at least one row with the formula in B, so that you can copy it down when the name list grows.