We help IT Professionals succeed at work.

How to find offset value based on a letter

Using Excel 2003... I have an order number (Ex 36146) and I've setup a "key" on a spread sheet to convert a number into a letter.  So the key is:

1 = A
2 = B
3= C
.....
0 = J

I would like to have excel convert 36146 to letters and I've done that with 6 cells using offset.

Two things I would like to change:

1. Instead of having 6 cells a single cell with the order number 36146 and have excel convert to letters (CFADF)

2. Reverse the result, in that if a user enters CFADF Excel will find the order number (36146).

Please  see attached file.... is this possible?

Thank you.
Order-To-Alpha.xls
Comment
Watch Question

ShumsManaging Director/Excel VBA Developer
Distinguished Expert 2018

Commented:
Hi Thandel.

Try below for Numeric to Alpha in D3 and drag across:
=IFERROR(INDEX($B$2:$B$11,MATCH(D$2,$A$2:$A$11,0)),"")

Open in new window

And for Alpha to Numeric try below in D8 and drag across:
=IFERROR(INDEX($A$2:$A$11,MATCH(D$7,$B$2:$B$11,0)),"")

Open in new window

Check attached...
Order-To-Alpha_v1.xlsx

Author

Commented:
Thanks but I am getting an error of #NAME? (I'm using Excel 2003)
ShumsManaging Director/Excel VBA Developer
Distinguished Expert 2018

Commented:
Sample you provided doesn't have error. Please upload the workbook, which has error

Author

Commented:
I'm getting the error when I implement your solution AND with your workbook.
"There is still no cure for the common birthday." ~John Glenn
Most Valuable Expert 2017
Distinguished Expert 2018
Commented:
Using user defined functions you can make the conversions without a key. In the attached workbook, A2 contains the numeric to alpha conversion of A1, and B2 contains the alpha to numeric conversion of B1.
29065054.xlsm

Author

Commented:
Holly cow never knew of that function... so easy thanks!
Martin Liss"There is still no cure for the common birthday." ~John Glenn
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Holly cow never knew of that function
You never heard of them because I wrote them using VBA. Go to Visual Basic by pressing Alt+f11 and then in the "Project - VBAProject" window expand "Modules" and select  "Module1". You'll then see the two UDFs (User Defined Functions).

In any case you're welcome and I'm glad I was able to help.

If you expand the “Full Biography” section of my profile you'll find links to some articles I've written that may interest you.

Marty - Microsoft MVP 2009 to 2017
              Experts Exchange MVE 2015
              Experts Exchange Top Expert Visual Basic Classic 2012 to 2017

Author

Commented:
Yup was just noticing... but its so simple love it thanks!  Nice code.