A common problem with VLOOKUPs is when the value you’re looking up is held in Excel as text, and the corresponding value in the lookup table is held as a number (or vice versa). Generally, values held in Excel as text have a single quote ‘ in front of them. It is a common misconception that reformatting a numeric cell so that it looks like text will mean that it is treated as text. Unfortunately Excel itself doesn’t help to dispel this myth by stating that “Text format cells are treated as text even when a number is in the cell.”. Choosing Format cells against a selected cell which holds a number , and then choosing the category Text from the Number tab will make the cell look like text, but a VLOOKUP function still won’t find it if it is looking in a lookup table which holds text values.
The reason is due to the way computers store numbers and characters (text). Computers store everything as numbers. Ultimately they are stored as binary ones and zeros, but in the simplest terms one storage location (one byte) holds a value as two hexadecimal digits with a maximum value of FF, which corresponds to 256. Each character is represented by a number corresponding to that number in a “character map” of 256 different characters. Therefore one character can be stored in one byte of information. Numbers on the other hand, can simply be held as hexadecimal digits, and therefore one byte of information can hold a number between 0 and 255, which looks more like 3 digits to us.
Excel has a function for converting text to a numeric, called “value”. To convert a text value in cell A1 to a numeric you would specify VALUE(A1). Because Excel can compound functions, the value being looked up could be converted to text using the VALUE function, and this could be substituted in the VLOOKUP where A1 was previously specified.
Excel has a further function for converting a numeric to a text value. For Excel to do this, it must know how you want the data to appear. For example, EXCEL holds time and date values as numerics. So if you were looking up a date value, formatted as a date, then chances are it is held as a number, and just formatted as a date. If the table you’re looking into is held a text values, then the VLOOKUP would fail, even though it looks visually that the data is there. There are many hundred different formats that Excel can format numbers in (not to mention custom user defined versions). Because of this, the VLOOKUP Assistant can’t test each one for VLOOKUPs. It can however, convert straight forward numbers to text.
To convert the value to text, Excel uses the function TEXT(value,format). There are many ways to specify format, but the simplest is simply “#”. Which tells Excel to format each digit as a number.
The VLOOKUP Assistant can however try the same function, adding a leading zero to the data (another common cause of failure is a missing leading zero – especially with telephone numbers). If Excel holds values as numbers, it will not hold the preceeding zero(s). The format which must be specified in the TEXT formula in this case is “0####”, where there should be one “#” for each digit in the number. The VLOOKUP Assistant works this function out for you, and displays the modified VLOOKUP.
If the VLOOKUP Assistant can find the data you’re looking for by converting it to text or a number, then it will display the new formula, and offer to copy it to the clipboard for you. You can then past it into your spreadsheet wherever you wish.