Exceljet

Quick, clean, and to the point

VLOOKUP with numbers and text

Excel formula: VLOOKUP with numbers and text
Generic formula 
=VLOOKUP(val&"",table,col,0)
Summary 

To use the VLOOKUP function to retrieve information from a table where the key values are numbers stored as text,  you can use a formula that concatenates an empty string ("") to the numeric lookup value, coercing it to text. In the example shown, the formula in H3 is:

=VLOOKUP(id&"",planets,2,0) // returns "Earth"

where id (H2) and planets (B3:B11) are named ranges

Note: The example is a workaround to the problem of mismatched numbers and text, which cause a #N/A error. If there is no mismatch, the workaround is not necessary and you can use a normal VLOOKUP formula.

Explanation 

A common VLOOKUP error is a mismatch between numbers and text. Most typically,  the lookup column in the table contains numeric values that look like numbers, but are in fact numbers stored as text. When a genuine number is passed into VLOOKUP as the first argument, the formula returns a #N/A error, even though there appears to be a match. The screen below shows an example of this problem:

Example of VLOOKUP error with numbers and text mismatch

The numbers in column B are actually text, so the numeric lookup value, 3, fails, even though it seems like VLOOKUP should match B5 and return "Earth". You can enter a number as a text value by preceding the number with a single quote (').

The best solution is to make sure the lookup values in the table are indeed numbers. However, if you don't have control over the table, you can modify the VLOOKUP formula to coerce the lookup value to match the type in the table. In the example shown, we coerce the numeric lookup value to text by concatenating an empty string:

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

And the revised formula takes care of the error:

 VLOOKUP numbers and text error solution

You could also do the same thing with a longer formula that utilizes the TEXT function to convert the number to text:

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

Both numbers and text

If you can't be certain when you'll have numbers and when you'll have text, you can cater to both options by wrapping VLOOKUP in the IFERROR function and using a formula that handles both cases:

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

Here, we first try a normal VLOOKUP formula that assumes both lookup value and the first column in the tables are numbers. If that throws an error, we try again with the revised formula. If that formula also fails, VLOOKUP will return an #N/A error as always.

Author 
Dave Bruns

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.

Download 200+ Excel Shortcuts

Get over 200 Excel shortcuts for Windows and Mac in one handy PDF.