Ranking Numbers with Text

[jerrygolfer]

I am trying to sort a list of serial numbers containing alpha an
numeric text into ascending order so LOOKUP will work. ie:
5KJJAHAS63PL84681 input into col. E
5KJJAHAS43PL84685
5KJJAHASx3PL84683
5KJJAHAS83PL84686
5KJJAHAS63PL84689
5KJJAHAS43PL84684
5KJJAHAS23PL84682
5KJJAHAS03PL84688
5KJJAHASx3PL84610
5KJJAHAS83PL84690
5KJJAHAS83PL84691
Only the ninth and 13 thru 17 digits change.
Worksheets are protected except for inputs.
I need the sort to use the LOOKUP function.
I tried =IF(E3>0,(RIGHT(E3,5)*1),999999) to RANK by the last 5
=RANK(D3,D$3$102,1)+COUNTIF(D$33,D3)-1,
but then LOOKUP doesn't recognize when I use
=OFFSET(E$3,MATCH(ROW()-2,C$3:C$102,0)-1,0).
Thanks for your tutorlege, Jerrygolfe

 

[Leo Heuser]

Jerry

Maybe you can use VLOOKUP() or HLOOKUP() instead
and set the fourth argument to FALSE, e.g.
=VLOOKUP(A1,G3:H56,4,FALSE)