VLOOKUP and #N/A Values

[rkingndayton]

Hello all - new to the forum and would like to start out by saying hello and thanks for hosting this forum.

I'm using Windows 7 64-bit and Excel 2010

The project I'm working on is for inventory control in a distributorship. I'm trying to use the VLOOKUP function to find a description and 12-month usage of a part number.

The problem I'm having is that the value comes back as #N/A UNLESS I go to the table and reenter the value (I changed it from text to general with no luck). I definitely don't have time to reenter every value (over 6,000 part numbers!)

Here's my formula: "=VLOOKUP(A2,'RUNNING 12 JUL 10 - JUN 11 CGS '!A2:AK1000,2,FALSE)"

I did a search through the forums and couldn't find that anyone was having the same issue. I'm assuming this is a rather easy fix, or maybe there's an easier, more effective way.

My goal is to use the data to automatically create a graph showing each items' usage.

 

[JazzSP8]

Hi - Welcome to the forum

If your getting an #NA before you retype the value then it suggests that the data in the tables cell is not correct for some reason, perhaps you've got a space (or spaces) before or after the data in the table?

EG: Test won't match with " Test" or "Test "

 

[ZPhantom]

Building on Jazzs' responce try:

"=VLOOKUP(TRIM(A2),TRIM('RUNNING 12 JUL 10 - JUN 11 CGS '!A2:AK1000),2,FALSE)"

 

[rkingndayton]

Building on Jazzs' responce try:

"=VLOOKUP(TRIM(A2),TRIM('RUNNING 12 JUL 10 - JUN 11 CGS '!A2:AK1000),2,FALSE)"

Tried it - didn't work.

The data comes from ODBC so there must be spaces after the part number; how would I quickly work around that? Copy/paste the part numbers then delete the old column?

 

[ZPhantom]

use the trim function to copy the information to the new column then try the vlookup on the new column.
dont delete the column until your sure it works!

out of curiosity when you tried teh formula above did it give an error or just a nonsence answer?

ZP

 

[rkingndayton]

Just the same #N/A

The only difference was that the part numbers that I did reenter didn't show up anymore.

I'll try that trim function (as soon as I find out exactly what it is lol) and reply in a few.

Thanks for your help!

 

[ZPhantom]

Trim takes the whitespace (" ") from the front and end of whatever is put inside it.

for instance Trim(" Fred ") = "Fred"

 

[JazzSP8]

out of curiosity when you tried teh formula above did it give an error or just a nonsence answer?

I don't think you can use TRIM on a range of cells?

 

[ZPhantom]

I don't think you can use TRIM on a range of cells?

that may well be an issue yes.....

 

[T. Valko]

Just the same #N/A

The only difference was that the part numbers that I did reenter didn't show up anymore.

I'll try that trim function (as soon as I find out exactly what it is lol) and reply in a few.

Thanks for your help!

The macro at this website will remove all leading/trailing and multiple interspersed char 32 space characters. It will also remove and/or convert char 160 non breaking spaces into standard char 32 space characters. It will work on text or numbers and the numbers will be converted to true numeric numbers.

I use this macro dozens of times every single day! It's a real time saver.

http://www.mvps.org/dmcritchie/excel/join.htm#trimall