Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 Jones<space <spaceJones<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 -- 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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Multiple Spreadsheet,Not sure what to use? Vlookup works for first | Excel Worksheet Functions | |||
vlookup almost works but not quite | Excel Discussion (Misc queries) | |||
Vlookup and left - works with all apart from 93% and 112% | Excel Discussion (Misc queries) | |||
VLOOKUP works in some cells but not others. | Excel Worksheet Functions | |||
VLOOKUP works sometimes | Excel Worksheet Functions |