Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Search/Lookup-Copy Help!
Hello. I'm trying to find a fast and easy way to have excel look up item
numbers listed on my orders (excel 2002) worksheet from the vendor's product list and then copy the data found from the vendor's product list to a new list so that I can import that data into my system. I have over 1000 item numbers so I really would like a fast and quick way to get this task accomplished. Does anyone have any idea how I can go about doing that? I'm a bit of a novice when it comes to excel functions so any help would be greatly appreciated. Thanks! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Search/Lookup-Copy Help!
Vlookup will do what you want (search through the help menu for related
information). Also, try using a Pivot Table (search help menu or Google). Regards, Ryan-- -- RyGuy "alimeishi" wrote: Hello. I'm trying to find a fast and easy way to have excel look up item numbers listed on my orders (excel 2002) worksheet from the vendor's product list and then copy the data found from the vendor's product list to a new list so that I can import that data into my system. I have over 1000 item numbers so I really would like a fast and quick way to get this task accomplished. Does anyone have any idea how I can go about doing that? I'm a bit of a novice when it comes to excel functions so any help would be greatly appreciated. Thanks! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Search/Lookup-Copy Help!
Hello.
Thanks for the info. May be I'm just not doing it correctly, but I can't seem to get it to work the way that I want. Is there a way I can set the Lookup_Value as a range? For example, A4:A789 is the list of the items that I've ordered and B4:Z19876 contains the product info and UPC numbers from the vendor that I need to extract. The difficulty in this problem is that for one product number (or item number) there could be 4 UPC codes associated with it. Will vlookup be able to extract all the UPC codes? Thanks! "ryguy7272" wrote: Vlookup will do what you want (search through the help menu for related information). Also, try using a Pivot Table (search help menu or Google). Regards, Ryan-- -- RyGuy "alimeishi" wrote: Hello. I'm trying to find a fast and easy way to have excel look up item numbers listed on my orders (excel 2002) worksheet from the vendor's product list and then copy the data found from the vendor's product list to a new list so that I can import that data into my system. I have over 1000 item numbers so I really would like a fast and quick way to get this task accomplished. Does anyone have any idea how I can go about doing that? I'm a bit of a novice when it comes to excel functions so any help would be greatly appreciated. Thanks! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Search/Lookup-Copy Help!
Ahh! Now I see what's going on. A limitation of the Vlookup is that it will
return the first value that matches the lookup value you are using. Try this function, which will overcome that limitation of the Vlookup: =IF(ROWS(B$1:B1)<=COUNTIF($A$1:$A$20,$E$1),INDEX($ B$1:$B$20,SMALL(IF($A$1:$A$20=$E$1,ROW($A$1:$A$20)-ROW($E$1)+1),ROWS(B$1:B1))),"") This array has to be entered with Ctrl+Shift+Enter (not just enter) Then, fill down as far as you need to... This is a little complex, but I believe you can use it to get the results you are looking for. Notice a few things: B-values represent the table array (product info and UPC numbers) A-values represent the list of items that you are working with E-values represent the one criteria that you are looking for in your list of items. Post back if you have any more questions. Regards, Ryan--- PS, here is another, similar, function that will yield the same results: =IF(ROWS($1:1)COUNTIF($A$1:$A$20,$E$1),"",INDEX($ B$1:$B$20,SMALL(IF(($A$1:$A$20=$E$1),ROW($A$1:$A$2 0)),ROWS($1:1)))) This array has to be entered with Ctrl+Shift+Enter (not just enter) -- RyGuy "alimeishi" wrote: Hello. Thanks for the info. May be I'm just not doing it correctly, but I can't seem to get it to work the way that I want. Is there a way I can set the Lookup_Value as a range? For example, A4:A789 is the list of the items that I've ordered and B4:Z19876 contains the product info and UPC numbers from the vendor that I need to extract. The difficulty in this problem is that for one product number (or item number) there could be 4 UPC codes associated with it. Will vlookup be able to extract all the UPC codes? Thanks! "ryguy7272" wrote: Vlookup will do what you want (search through the help menu for related information). Also, try using a Pivot Table (search help menu or Google). Regards, Ryan-- -- RyGuy "alimeishi" wrote: Hello. I'm trying to find a fast and easy way to have excel look up item numbers listed on my orders (excel 2002) worksheet from the vendor's product list and then copy the data found from the vendor's product list to a new list so that I can import that data into my system. I have over 1000 item numbers so I really would like a fast and quick way to get this task accomplished. Does anyone have any idea how I can go about doing that? I'm a bit of a novice when it comes to excel functions so any help would be greatly appreciated. Thanks! |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Search/Lookup-Copy Help!
Hi Ryan.
Thanks for sending the formula, but I can't seem to get it to work. I know I'm doing something wrong, but don't know what it is. I followed what you had written but I get a "#NUM!" in the cell where I typed in the formula. If it's not asking too much, can I email you my worksheet to figure out what the problem is? Or would you like me to just give the details via this post? Thanks for all your help! "ryguy7272" wrote: Ahh! Now I see what's going on. A limitation of the Vlookup is that it will return the first value that matches the lookup value you are using. Try this function, which will overcome that limitation of the Vlookup: =IF(ROWS(B$1:B1)<=COUNTIF($A$1:$A$20,$E$1),INDEX($ B$1:$B$20,SMALL(IF($A$1:$A$20=$E$1,ROW($A$1:$A$20)-ROW($E$1)+1),ROWS(B$1:B1))),"") This array has to be entered with Ctrl+Shift+Enter (not just enter) Then, fill down as far as you need to... This is a little complex, but I believe you can use it to get the results you are looking for. Notice a few things: B-values represent the table array (product info and UPC numbers) A-values represent the list of items that you are working with E-values represent the one criteria that you are looking for in your list of items. Post back if you have any more questions. Regards, Ryan--- PS, here is another, similar, function that will yield the same results: =IF(ROWS($1:1)COUNTIF($A$1:$A$20,$E$1),"",INDEX($ B$1:$B$20,SMALL(IF(($A$1:$A$20=$E$1),ROW($A$1:$A$2 0)),ROWS($1:1)))) This array has to be entered with Ctrl+Shift+Enter (not just enter) -- RyGuy "alimeishi" wrote: Hello. Thanks for the info. May be I'm just not doing it correctly, but I can't seem to get it to work the way that I want. Is there a way I can set the Lookup_Value as a range? For example, A4:A789 is the list of the items that I've ordered and B4:Z19876 contains the product info and UPC numbers from the vendor that I need to extract. The difficulty in this problem is that for one product number (or item number) there could be 4 UPC codes associated with it. Will vlookup be able to extract all the UPC codes? Thanks! "ryguy7272" wrote: Vlookup will do what you want (search through the help menu for related information). Also, try using a Pivot Table (search help menu or Google). Regards, Ryan-- -- RyGuy "alimeishi" wrote: Hello. I'm trying to find a fast and easy way to have excel look up item numbers listed on my orders (excel 2002) worksheet from the vendor's product list and then copy the data found from the vendor's product list to a new list so that I can import that data into my system. I have over 1000 item numbers so I really would like a fast and quick way to get this task accomplished. Does anyone have any idea how I can go about doing that? I'm a bit of a novice when it comes to excel functions so any help would be greatly appreciated. Thanks! |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Search/Lookup-Copy Help!
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
help with search/lookup | Excel Worksheet Functions | |||
Search/Lookup and Place | Excel Worksheet Functions | |||
How to search column, copy row, and copy to another sheet in same | Excel Discussion (Misc queries) | |||
Search and lookup | Excel Worksheet Functions | |||
LOOKUP w/ multiple search columns? | Excel Discussion (Misc queries) |