VLOOKUP works for some cells but not all Help!

[Tacrier]

On a quarterly basis I import information from another program into excel so
that I can format the information into a specific format.

When the info. is first imported it is all in column A so I use text to
columns to move the info. over into it's own columns.

After that there is one column of info. that I am missing, "Vendor Name", so
I use the VLOOKUP to plug in the name from another spreadsheet that has the
Vendor Name info.

The VLOOKUP has worked before but for some reason this time, it returns a
#NA result for most of the column even though there is a match. I have tried
changing the format to text, then general then text again. It doesn't work.

Any suggestions as to how I can get the VLOOKUP to work?

 

[T. Valko]

The most common cause is leading/trailing spaces.

Your lookup_value might be Jones but in the table it might be entered as:

<space>Jones
Jones<space>
<space>Jones<space>

There is a macro at this site that will clean all those spaces from your
data:

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

 

[Bob Umlas, Excel MVP]

Most likely the value you're looking up has many trailing spaces -- if you
highlight the reference in the VLOOKUP formula & press the F9 key, you'll
probably see siomething like "IBM " instead of "IBM" because of the
text-to-columns.
So instead of =VLOOKUP(B3,....), use
=VLOOKUP(TRIM(B3),.....)

HTH
Bob Umlas

 

[Tacrier]

Awesome! That worked!

Thank you.

Most likely the value you're looking up has many trailing spaces -- if you
highlight the reference in the VLOOKUP formula & press the F9 key, you'll
probably see siomething like "IBM " instead of "IBM" because of the
text-to-columns.
So instead of =VLOOKUP(B3,....), use
=VLOOKUP(TRIM(B3),.....)

HTH
Bob Umlas