Excel - assigning a cell number based on another cell number

Factual Questions
#1

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.

#2

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.

#3

Just look it up in a list.

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

Thanks for your help.

#4

VLOOKUP, definitely.

#5

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.

#6

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