Sometimes data that comes into Excel with code numbers formatted as text. This can stop VLOOKUP functions from working and return the dreaded #N/A error. With a couple of tweaks you can lookup both real numbers and text numbers in the one formula.

The trick is to use the IFERROR function with two VLOOKUP functions to handle both real numbers and text numbers.

The table below has a combination of text number and real numbers.  Numbers formatted as text are left aligned.

Normally this would prove a problem for a VLOOKUP. We can use the IFERROR function to cope with both number types. The table below has the code to lookup in the yellow cells in column E.

I have entered both real and text numbers in the yellow cells. Text numbers align left.

The formula for cell F3 that has been copied right and down is

=IFERROR(VLOOKUP(""&$E3,$A$2:$C$9,F$1,0),IFERROR(VLOOKUP($E3*1,$A$2:$C$9,F$1,0),"Missing"))

This looks a bit daunting but it is two VLOOKUPS and two IFERRORS.

If the code is not in the table, or the input cell is blank, the formula will display the word “Missing” rather than #N/A.

There are two important parts to the way these VLOOKUP functions work.

1. In the first VLOOKUP

VLOOKUP(""&$E3,$A$2:$C$9,F$1,0)

I have used  “”&$E3 to define the code to look up. This converts a real number into a text number and handles all the text numbers in the data table.

2. The second is in the second VLOOKUP

VLOOKUP($E3*1,$A$2:$C$9,F$1,0)

I have multiplied the entry in E3 by 1. This converts any text numbers entered into real numbers.

By using those two techniques it doesn’t matter whether a real number or a text number is entered into cell E3 and it doesn’t matter if the entries in the table are real or text numbers.

Two other things worth noting in general about the above VLOOKUPS.

First – I haven’t keyed in the column number (third argument). I have used cell F1 as a helper cell. This enables me to copy the formula across without having to change it.

Second – I have used 0 instead of FALSE to specify the search type (fourth argument) as it quicker to type and does exactly the same thing.