My spreadsheet has a column of employee numbers that is alphanumeric. The numeric portion tells me which location they belong to. I need to develop a formula that will read the numeric portion of a cell and assign a location code so I can then sort it. I believe none of the formulas I have tried work correctly because the locations are numeric. I have included a table of locations if that helps. The result should convert the numeric portion of the employee to the alpha location code listed in the table.
In other words, the employee listed on row two has a location of 1250 in cell B2. In cell F2, the result should be "T". The formula in column F does not give me the correct answer.
Sure would appreciate some help!
Last edited by DianeP; 01-05-2012 at 11:39 AM.
DianeP,
Attached is a modified version of your example workbook. I reformatted the table layout to be excel friendly. Then, in cell F2 I used this formula:
=VLOOKUP(LOOKUP(9E+300,--LEFT(A2,ROW($A$1:INDEX(A:A,LEN(A2))))),$I$2:$J$37,2,FALSE)
And copied down.
Hope that helps,
~tigeravatar
Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble
Another way is,
Convert the table I3:N17 to a valid numbers, the use this formula in E2,
=IFERROR(INDEX(I$2:N$2,SMALL(IF($I$3:$N$17=LOOKUP(9E300,--LEFT(B2,ROW(A$1:A$20))),COLUMN($I2:$N2)-COLUMN($I2)+1),1)),"")
Confirmed with CTRL+SHIFT+ENTER, then copy down.
HTH; Haseeb
If your problem is solved, please say so clearly, and mark your thread as Solved:
Forum Rules & How to Mark a thread as SOLVED
Two very interesting solutions to this problem that both work perfectly! I was not very familiar with either of these types of formulas and have now done some research to help me understand. Thank you both so much for your help.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks