Index Match to convert alphanumeric to pure numberic

[dj123171]

Hello, I am having a consistent #N/A error when I try to convert a Base36 alpha numeric string into a pure numeric string that represents where in the Base36 alphabet the digit represents. For example, for Base 36, the number 0 is 0, the letter F would represent 15 and W would represent 32. The alphabet is "0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ" and I listed it in Column A, with numeric representations in Column B, represented here:

Column A	Column B
0	0
1	1
2	2
3	3
4	4
5	5
6	6
7	7
8	8
9	9
A	10
B	11
C	12
D	13
E	14
F	15
G	16
H	17
I	18
J	19
K	20
L	21
M	22
N	23
O	24
P	25
Q	26
R	27
S	28
T	29
U	30
V	31
W	32
X	33
Y	34
Z	35

<tbody>
</tbody>

If have C3 = W on the same sheet, I wanted to use the Index-Match to do the following:

D3=Index($A$1:$B$36,match(C3,$A$1:$A$36,0),2)

I am expecting the value of D3 = 32 but I get #N/A. Any suggestions here? Greatly appreciated!

 

[cunningAce]

There is nothing wrong with the formula. Works fine?

 

[Haluk]

Your formula seems to be correct but you can use VLOOKUP function also, which is simpler and shorter.

=VLOOKUP(C3,A1:B36,2,0)

 

[MARZIOTULLIO]

d3=INDEX($B$1:$B$36,MATCH(C3,$A$1:$A$36,0))

 

[shg]

The list is sorted, so you can do a binary search, and the data is sequential, so you don't need the second column:

=MATCH(C3, $A$1:$A$36) - 1

 

[dj123171]

d3=INDEX($B$1:$B$36,MATCH(C3,$A$1:$A$36,0))

Thanks everyone, I agree that the formula works, but ONLY for numbers and not the letters in the Base36 alphabet when trying to back convert to a number. Here is the result of an example:

7	7
6	6
W	#N/A
F	#N/A
3	3

<tbody>
</tbody>

So, it works when it looks up a number but not a letter. Any insights? Vlookup can work, but there's issues with A->Z and other complications that I'd like to avoid with Index match.

 

[shg]

Don't see the problem:

	A​	B​	C​	D​	E​
1​	0​		​
2​	1​		2​	2​	D2: =MATCH(C2, $A$1:$A$36) - 1
3​	2​		A​	10​
4​	3​		W​	32​
5​	4​		Z​	35​
6​	5​		​
7​	6​
8​	7​
9​	8​
10​	9​
11​	A​
12​	B​
13​	C​
14​	D​
15​	E​
16​	F​
17​	G​
18​	H​
19​	I​
20​	J​
21​	K​
22​	L​
23​	M​
24​	N​
25​	O​
26​	P​
27​	Q​
28​	R​
29​	S​
30​	T​
31​	U​
32​	V​
33​	W​
34​	X​
35​	Y​
36​	Z​

 

[dj123171]

Match function worked great, thank you! Here is the result, side by side:

Colmn 1	Colum 2	Column 3
0	0	0
1	1	1
2	2	2		Data	Index	Match
3	3	3		7	7	7
4	4	4		6	6	6
5	5	5		W	#N/A	32
6	6	6		F	#N/A	15
7	7	7		3	3	3
8	8	8
9	9	9
10	A	10
11	B	11
12	C	12
13	D	13
14	E	14
15	F	15
16	G	16
17	H	17
18	I	18
19	J	19
20	K	20
21	L	21
22	M	22
23	N	23
24	O	24
25	P	25
26	Q	26
27	R	27
28	S	28
29	T	29
30	U	30
31	V	31
32	W	32
33	X	33
34	Y	34
35	Z	35

<!--StartFragment--> <colgroup><col width="65" span="7" style="width:65pt"> </colgroup><tbody>
<!--EndFragment--></tbody>

<colgroup><col width="65" span="7" style="width:65pt"></colgroup><tbody><!--EndFragment--></tbody>

 

[shg]

Or dispense with the list and use

=SEARCH(C2, "0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ") - 1

 

[Rick Rothstein]

To go from a Decimal digit to Base36 "digit"...

=CHAR(A1+48+7*(A1>9))

To go from Base36 "digit" to Decimal digit...

=CODE(A1)-48-7*ISTEXT(B1)