VLOOKUP with numbers and text

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

Our customers love us!
“The expert was absolutely amazing and stuck with me the whole way through. They were polite, patient, seemed to want to genuinely help me and provided a solution that I would never have managed otherwise. I could not be more thankful for their support and solution. Thank you!” - - Chris T, in California

Leave a Comment

avatar