# Excel - assigning a cell number based on another cell number **URL:** \ **Category:** Factual Questions\ **Created:** [November 25, 2013, 4:47pm UTC](https://boards.straightdope.com/t/excel-assigning-a-cell-number-based-on-another-cell-number/674763 "2013-11-25T16:47:54Z")\ **Posts on this page:** 6\ **Page:** 1
**Author:** ![brewha](https://avatars.discourse-cdn.com/v4/letter/b/91b2a8/32.png) [@brewha](https://boards.straightdope.com/u/brewha)\ **Post date:** [November 25, 2013, 4:47pm UTC](https://boards.straightdope.com/t/excel-assigning-a-cell-number-based-on-another-cell-number/674763/1 "2013-11-25T16:47:54Z")
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. ---
**Author:** ![CookingWithGas](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/cookingwithgas/32/485_2.png) [@CookingWithGas](https://boards.straightdope.com/u/CookingWithGas)\ **Post date:** [November 25, 2013, 5:34pm UTC](https://boards.straightdope.com/t/excel-assigning-a-cell-number-based-on-another-cell-number/674763/2 "2013-11-25T17:34:23Z")
> [@brewha](#): > > Many different 5 digit part numbers will share the same 2 digit code. 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. ---
**Author:** ![brewha](https://avatars.discourse-cdn.com/v4/letter/b/91b2a8/32.png) [@brewha](https://boards.straightdope.com/u/brewha)\ **Post date:** [November 25, 2013, 6:11pm UTC](https://boards.straightdope.com/t/excel-assigning-a-cell-number-based-on-another-cell-number/674763/3 "2013-11-25T18:11:36Z")
> [@CookingWithGas](#): > > 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. ---
**Author:** ![babygoat666](https://avatars.discourse-cdn.com/v4/letter/b/2acd7d/32.png) [@babygoat666](https://boards.straightdope.com/u/babygoat666)\ **Post date:** [November 25, 2013, 6:20pm UTC](https://boards.straightdope.com/t/excel-assigning-a-cell-number-based-on-another-cell-number/674763/4 "2013-11-25T18:20:38Z")
VLOOKUP, definitely. ---
**Author:** ![BubbaDog](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/bubbadog/32/333_2.png) [@BubbaDog](https://boards.straightdope.com/u/BubbaDog)\ **Post date:** [November 25, 2013, 6:31pm UTC](https://boards.straightdope.com/t/excel-assigning-a-cell-number-based-on-another-cell-number/674763/5 "2013-11-25T18:31:56Z")
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. ---
**Author:** ![brewha](https://avatars.discourse-cdn.com/v4/letter/b/91b2a8/32.png) [@brewha](https://boards.straightdope.com/u/brewha)\ **Post date:** [November 25, 2013, 8:07pm UTC](https://boards.straightdope.com/t/excel-assigning-a-cell-number-based-on-another-cell-number/674763/6 "2013-11-25T20:07:17Z")
That’s exactly it. I got it programmed and working! Thanks much for everyone’s help.