VLOOKUP Google Sheets not finding entry from formula

Question

Here is a link to my sheet.

I am trying to do some base 36 to binary encoding and VLOOKUP claims to not be able to find the values of column E even though they are clearly there.

I've narrowed down the problem, if I put in a specific value (say '7') into VLOOKUP, it can find it just fine. It is when I ask it to find a value which itself is the result of some equation that VLOOKUP fails. I need to be able to change Encoded Value and get the apporpriate decimal number.

Also, if I change the Encoded Value to, say, AAAA, then VLOOKUP does just fine and gives values of 10 for all of them. It seems to only be an issue when it is looking for numerics from Encoded Value.

Thanks!

Answer 1

The problem is data type inconsistency: text vs numeric. Your formula

=IF(LEN($A$2)>0, MID($A$2,LEN($A$2),1), 0)

returns a string "7". Your lookup table has number 7. No match.

A way to convert strings to numbers is to add 0:

=IF(LEN($A$2)>0, 0 + MID($A$2,LEN($A$2),1), 0)

However, I understand you are working with base-36 numbers, where digits can also be A, B, C, and so on. in that case, the above would not work for those digits. Instead, to have consistent data type you need to convert column E entries to text.

The easiest way to put "7" in a spreadsheet cell is to precede it by a single apostrophe: enter '7. The apostrophe will not be a part of cell content, it only designates the type of cell content as a string.

One can also perform conversion with =text(7, "0")