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:
[TABLE="width: 0"]
<tbody>[TR]
[TD="align: center"]Column A[/TD]
[TD="align: center"]Column B[/TD]
[/TR]
[TR]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]2[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]3[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]4[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]5[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: center"]6[/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: center"]7[/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD="align: center"]8[/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD="align: center"]9[/TD]
[/TR]
[TR]
[TD="align: center"]A[/TD]
[TD="align: center"]10[/TD]
[/TR]
[TR]
[TD="align: center"]B[/TD]
[TD="align: center"]11[/TD]
[/TR]
[TR]
[TD="align: center"]C[/TD]
[TD="align: center"]12[/TD]
[/TR]
[TR]
[TD="align: center"]D[/TD]
[TD="align: center"]13[/TD]
[/TR]
[TR]
[TD="align: center"]E[/TD]
[TD="align: center"]14[/TD]
[/TR]
[TR]
[TD="align: center"]F[/TD]
[TD="align: center"]15[/TD]
[/TR]
[TR]
[TD="align: center"]G[/TD]
[TD="align: center"]16[/TD]
[/TR]
[TR]
[TD="align: center"]H[/TD]
[TD="align: center"]17[/TD]
[/TR]
[TR]
[TD="align: center"]I[/TD]
[TD="align: center"]18[/TD]
[/TR]
[TR]
[TD="align: center"]J[/TD]
[TD="align: center"]19[/TD]
[/TR]
[TR]
[TD="align: center"]K[/TD]
[TD="align: center"]20[/TD]
[/TR]
[TR]
[TD="align: center"]L[/TD]
[TD="align: center"]21[/TD]
[/TR]
[TR]
[TD="align: center"]M[/TD]
[TD="align: center"]22[/TD]
[/TR]
[TR]
[TD="align: center"]N[/TD]
[TD="align: center"]23[/TD]
[/TR]
[TR]
[TD="align: center"]O[/TD]
[TD="align: center"]24[/TD]
[/TR]
[TR]
[TD="align: center"]P[/TD]
[TD="align: center"]25[/TD]
[/TR]
[TR]
[TD="align: center"]Q[/TD]
[TD="align: center"]26[/TD]
[/TR]
[TR]
[TD="align: center"]R[/TD]
[TD="align: center"]27[/TD]
[/TR]
[TR]
[TD="align: center"]S[/TD]
[TD="align: center"]28[/TD]
[/TR]
[TR]
[TD="align: center"]T[/TD]
[TD="align: center"]29[/TD]
[/TR]
[TR]
[TD="align: center"]U[/TD]
[TD="align: center"]30[/TD]
[/TR]
[TR]
[TD="align: center"]V[/TD]
[TD="align: center"]31[/TD]
[/TR]
[TR]
[TD="align: center"]W[/TD]
[TD="align: center"]32[/TD]
[/TR]
[TR]
[TD="align: center"]X[/TD]
[TD="align: center"]33[/TD]
[/TR]
[TR]
[TD="align: center"]Y[/TD]
[TD="align: center"]34[/TD]
[/TR]
[TR]
[TD="align: center"]Z[/TD]
[TD="align: center"]35[/TD]
[/TR]
</tbody>[/TABLE]

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!

 

[shg]

Those two functions are not compatible:

[Table="width:, class:grid"][tr][td="bgcolor:#C0C0C0"][/td][td="bgcolor:#C0C0C0"]

A​

[/td][td="bgcolor:#C0C0C0"]

B​

[/td][/tr][tr][td="bgcolor:#C0C0C0"]

2​

[/td][td]

9​

[/td][td]A2: Input[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

3​

[/td][td="bgcolor:#E5E5E5"]9[/td][td]A3: =CHAR(A2+48+7*(A2>9))[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

4​

[/td][td="bgcolor:#E5E5E5"]

2​

[/td][td]A4: =CODE(A3)-48-7*ISTEXT(A3)[/td][/tr]
[/table]

 

[Rick Rothstein]

Those two functions are not compatible:

[Table="width:, class:grid"][tr][td="bgcolor:#C0C0C0"][/td][td="bgcolor:#C0C0C0"]

A​

[/td][td="bgcolor:#C0C0C0"]

B​

[/td][/tr][tr][td="bgcolor:#C0C0C0"]

2​

[/td][td]

9​

[/td][td]A2: Input[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

3​

[/td][td="bgcolor:#E5E5E5"]9[/td][td]A3: =CHAR(A2+48+7*(A2>9))[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

4​

[/td][td="bgcolor:#E5E5E5"]

2​

[/td][td]A4: =CODE(A3)-48-7*ISTEXT(A3)[/td][/tr]
[/table]

I think the problem may have to do with real numbers versus text numbers. I think these two should work no matter which...

To go from a Decimal number (0 to 36) to Base36 "digit"...

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

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

=CODE(A1)-48-7*ISERR(--A1)