Excel Tip #3: Why does my VLOOKUP return #N/A?

Mar 13, 2011 -


Have you ever had an Excel formula not work, but you've checked the formula multiple times already? You are certain that the formula is correct, but excel returns back #N/A or a blank. Do you wonder what is going on?

Due to Excel's stupidity, the format of your data you want returned has to match the format of the data you are pulling from. From this is not the case, your vlookup will return a bunch of retarded #N/As or blanks.

For example, let's say you are looking for the purchase order 6555 in the table below, and you want to return the amount from column 3 of that table. For one reason or another you may have extracted the below information from a PDF by saving it into notepad and then using the Text to Excel conversion to convert it to Excel. Somewhere along the way the format of the data went haywire.

Today is your lucky day, there is a solution. Find a blank cell and type in any number. Then copy the cell and paste special over the problem cells using past 'values' and 'multiply. Now the number that Excel though was just text (or some other crap) is now a number that vlookup understands.

Copyright © 2007- StockKevin. Disclaimer. All Rights Reserved.