# 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
Shums Faruk

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)),"")
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)),"")
Check attached...
Order-To-Alpha_v1.xlsx
thandel

Thanks but I am getting an error of #NAME? (I'm using Excel 2003)
Sample you provided doesn't have error. Please upload the workbook, which has error

I'm getting the error when I implement your solution AND with your workbook.
Martin Liss

membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.

Holly cow never knew of that function... so easy thanks!
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