Excel - assigning a cell number based on another cell number

I tried google. I tried excel help. But what I am trying to do isn’t easily asked in a search window. So, hopefully real people can tell me if what I want to do is possible.

Basically, I need to populate a column with a two digit number. The number that shows up in the cell is based on a 5 digit part number.

The 5 digit part numbers are automatically put into the spread sheet. I need to add a column that “reads” the part number and returns the proper 2 digit code. Many different 5 digit part numbers will share the same 2 digit code.

So, as an example

50001 = 01
50002 = 01
50003 = 01
50012 = 02
53837 = 02
43928 = 02
33828 = 03
33228 = 04
99483 = 04
There are around 150 5 digit codes and I need to assign them to about 40 2 digit codes.

Is there a way for excel to do this automatically?

I’m going to keep searching for the answer, but if an excel guru know how to do this, I’d appreciate the help.

That bit is what makes this potentially hard. Your example does not give any indication of how the two-digit code is derived from the five-digit part number. Is there an algorithm, or do you just have to look it up in a list?

If there’s an algorithm I can show you how to create an Excel formula for it. Otherwise you will have to create a list in one column of all part numbers, and in the next column all corresponding two-digit codes, then use VLOOKUP to find the code corresponding to a specific part number.

Just look it up in a list.

VLOOKUP? Lemme see if I can figure out how that works.

Thanks for your help.

VLOOKUP, definitely.

You could make a table (looks like its 150 rows from your explanation) that has every 5 digit code in column 1 (helps if they are in numerical order) and the matching 2 digit code in column 2.

A B
50001 01
50002 05
50002 01
.
.
.
99843 05

Then all you need is a lookup function

= vlookup(“code”, A2:B151, 2)

I’d put the table on a separate sheet of the workbook, name the two column range that holds the table values as “CodeLookup”

Then when a 5 digit code is entered into example the “cell A45”, you would have a formula in B45 that is =Vlookup(A45,Codelookup,2)
That formula could be copied down next to any list of codes in column a and it would do a proper lookup.

That’s exactly it. I got it programmed and working! Thanks much for everyone’s help.