VLOOKUP works for some cells but not all Help!

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?

The most common cause is leading/trailing spaces.

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

spaceJones
Jonesspace
spaceJonesspace

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

--
Biff
Microsoft Excel MVP


"Tacrier" . wrote in message
...
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?

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" wrote:

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?

Awesome! That worked!

Thank you.

"Bob Umlas, Excel MVP" wrote:

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" wrote:

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?