Search/Lookup-Copy Help!

[alimeishi]

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!

 

[ryguy7272]

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--

 

[alimeishi]

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]

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$20)),ROWS($1:1))))
This array has to be entered with Ctrl+Shift+Enter (not just enter)

 

[alimeishi]

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]

Sure, send me an email at:
(e-mail address removed)

Take out the 'my' part. I just found out that there is some technology out
there that scans through people's emails on discussion groups such as these,
and others, and then spams the unsuspecting victims. Just yesterday I spent
6 hours dealing with spam issues and virus issues on a friend's computer --
I don't want to do that again anytime soon.