If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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? |
#2
|
|||
|
|||
VLOOKUP works for some cells but not all Help!
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? |
#3
|
|||
|
|||
VLOOKUP works for some cells but not all Help!
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? |
#4
|
|||
|
|||
VLOOKUP works for some cells but not all Help!
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? |
Thread Tools | |
Display Modes | |
|
|