Recently, someone asked me for help with VLOOKUP problem numbers:
- He had a list of 8-digit numbers.
- In the next column, there was a formula to get the first 3 numbers: =LEFT(A2,3)
- Based on that number, a VLOOKUP formula should find the category in a lookup table
- Problem – Instead of a category name, the result is an #N/A error.
The VLOOKUP formula looks okay, so why doesn’t it work?
The VLOOKUP Problem Numbers Challenge
In my newsletter last week, I described this VLOOKUP problem, and asked readers to send me their solutions.
They downloaded the sample file, to see what was wrong, and figured out how to fix it.
You can download the file too, from the VLOOKUP Numbers Problem page on my website.
What’s the Problem?
Before you can fix a problem in Excel, you have to figure out what that problem is!
Maybe you’ve run into a similar situation before, so you know where to look. If you don’t have any experience with this type of VLOOKUP problem, and you can find VLOOKUP troubleshooting tips here.
One place to start is in the Function Arguments window:
- Select the VLOOKUP formula cell, and click the fx button in the Formula Bar.
- In the Function Arguments window, check the Lookup_value and Table_array values.
Do you see any differences in the values, or obvious problems? In this screen shot, the numbers look different.
- The Lookup_value numbers are wrapped with quote marks – they are text, not real numbers
- The Table_array numbers have NO quote marks – they are real numbers
That shows why the lookup values aren’t being found – Excel doesn’t match text numbers with real numbers.
Solving the Problem
After you’ve done the troubleshooting, it’s time to fix the problem. The text numbers can be converted to real numbers, so that Excel will find a match for them in the lookup table.
There are different ways to do that, and in the solutions that my newsletter readers sent, most used the VALUE function, or multiplied the code by 1.
Some people changed the 3-digit code formula in column B:
=VALUE(LEFT(A2,3))
Others made a change in the VLOOKUP formula:
=VLOOKUP([@Code]*1,tblCat,2,0)
Download the Sample File
To see the VLOOKUP problem numbers example, with troubleshooting tips and solutions, go to the VLOOKUP Numbers Problem page on my website. Read through the details and troubleshooting suggestions, and download the sample file.
Maybe you’ll come up with a new solution to the problem!
___________________