VLOOKUP (Vertical Lookup) with text and numbers

## Formula

`=VLOOKUP(val&"",table,col,0)`

## Elaboration

There is a problem in this formula VLOOKUP, that is, there is often a miscarriage between text and numbers. The lookup value is contained in the table and its first column, and the number is stored as a text. In some cases, the table contains the number, but the value looked up is usually stored as a text, but it is itself a number. If there are any problems, the formula will return an error #N/A even if it matches. The solution to this problem is to have to convert the 1st column in the table and look for the same type of value. In addition, you can change VLOOKUP formula to make type in a table. The following are the original and revised forms of the formula:

**=VLOOKUP(id,planets,2,0)** **// original**

**=VLOOKUP(id&"",planets,2,0) // revised**

## Working of formula

Here is another choice to put TEXT in the function for its conversion to text.

**=VLOOKUP(TEXT(id,"@"),planets,2,0)**

In case, you have numbers as well as the words

If you are not sure when there is text when there are numbers, and you can satisfy both options by writing a formula that handles both cases and wrapping VLOOKUP in IFERROR. Sometimes you are not sure if it is a number or text, so you can add IFERROR in other ways, so the formula would look like this:

**=IFERROR(VLOOKUP(id,planets,3,0),VLOOKUP(id&"",planets,3,0))**

*Figure1. Example of VLOOKUP with numbers and text*

## Leave a Comment