Index Match to convert alphanumeric to pure numberic

dj123171 · Feb 16, 2018

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!

cunningAce · Feb 16, 2018

There is nothing wrong with the formula. Works fine?

Haluk · Feb 16, 2018

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

Code:

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

MARZIOTULLIO · Feb 16, 2018

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

shg · Feb 16, 2018

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 · Feb 16, 2018

MARZIOTULLIO said:
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:

[TABLE="width: 130"]
<tbody>[TR]
[TD="class: xl63, width: 65"]7[/TD]
[TD="class: xl64, width: 65"]7[/TD]
[/TR]
[TR]
[TD="class: xl63"]6[/TD]
[TD="class: xl64"]6[/TD]
[/TR]
[TR]
[TD="class: xl63"]W[/TD]
[TD="class: xl64"]#N/A[/TD]
[/TR]
[TR]
[TD="class: xl63"]F[/TD]
[TD="class: xl64"]#N/A[/TD]
[/TR]
[TR]
[TD="class: xl63"]3[/TD]
[TD="class: xl64"]3[/TD]
[/TR]
</tbody>[/TABLE]

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 · Feb 16, 2018

Don't see the problem:

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

A

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

B

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

C

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

D

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

E

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

1

[/td][td]

0

[/td][td][/td][td]

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

2

[/td][td]

1

[/td][td][/td][td]

2

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

2

[/td][td]D2: =MATCH(C2, $A$1:$A$36) - 1[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

3

[/td][td]

2

[/td][td][/td][td]

A

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

10

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

4

[/td][td]

3

[/td][td][/td][td]

W

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

32

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

5

[/td][td]

4

[/td][td][/td][td]

Z

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

35

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

6

[/td][td]

5

[/td][td][/td][td]

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

7

[/td][td]

6