VLookup and leading zeros

"leading zeros" can be put in as part of the number, or as part of the formatting. Click on the number in Sheet1, column A, and look in the Formula bar. If the number is displayed there without the zero's, then it's a number formatted as text with the leading zeros. Now click in your Column C and do the same thing. I'm willing to bet in one area you have a number formatted as text, and in the other you have a text number, which has the leading zeros in it's values. In other words, on the sheet you might have two numbers that you can see plain as day say "000254". Clicking on each, one shows in the formula bar as "000254", the other as "254". VLookup will return an "N/A" because it can't find a match.

Depending on which one the problem is, one of the solutions below should work.

Please Login or Register  to view this content.

After determining which solution boils out the right answer, you can use IFERROR to apply either solution, like this:

Please Login or Register  to view this content.

Your formula does appear to work. See the attached.

Converting each to text in the formatting palette took care of the problem without having to adjust my original VLookup formula. Thank you jomili ... just pointing out how each was appearing in the formula bar helped. I think your second formula is missing a )