LOOKUP does not work with MID when a number

[Guest]

=LOOKUP(A2,C34)
WHERE A2 cell is =MID(A1,ROW($4:$4),1)
C3=0
D3=A
C4=A
D4=0
When text in A1 = ABC0123 the lookup value returns an error (#N/A). If the 0
is replaced by a letter the value for the LOOKUP returns an A, which is what
I want.

The question: How can I get the LOOKUP cell to return a value when a number
is entered? I've already tried =LOOKUP(VALUE(A2),C34)) and it gives me a
value, but then the reverse is true; the LOOKUP cell returns an error if A2
is a letter.

All cells are formatted as text.

Any suggestions would be greatly appreciated!

 

[Guest]

Additionally, if the the A2 cell is replaced with 0 the LOOKUP works fine.

 

[Max]

Try replacing the formula in A2 with:
=IF(ISNUMBER(MID(A1,ROW($4:$4),1)+0),MID(A1,ROW($4:$4),1)+0,MID(A1,ROW($4:$4),1))

---

 

[Guest]

Thanks Max! It works great! Very slick.

Try replacing the formula in A2 with:
=IF(ISNUMBER(MID(A1,ROW($4:$4),1)+0),MID(A1,ROW($4:$4),1)+0,MID(A1,ROW($4:$4),1))

 

[Max]

Welcome, glad it helped.