Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Excel / Worksheet Functions / February 2008

Tip: Looking for answers? Try searching our database.

Search/Lookup-Copy Help!

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
alimeishi - 05 Feb 2008 06:09 GMT
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 - 05 Feb 2008 14:25 GMT
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--

Signature

RyGuy

> 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
[quoted text clipped - 4 lines]
> a bit of a novice when it comes to excel functions so any help would be
> greatly appreciated.  Thanks!
alimeishi - 05 Feb 2008 19:51 GMT
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!

> 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).
[quoted text clipped - 10 lines]
> > a bit of a novice when it comes to excel functions so any help would be
> > greatly appreciated.  Thanks!
ryguy7272 - 06 Feb 2008 18:27 GMT
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)

Signature

RyGuy

> Hello.
>
[quoted text clipped - 20 lines]
> > > a bit of a novice when it comes to excel functions so any help would be
> > > greatly appreciated.  Thanks!
alimeishi - 11 Feb 2008 06:39 GMT
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!

> 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.
[quoted text clipped - 44 lines]
> > > > a bit of a novice when it comes to excel functions so any help would be
> > > > greatly appreciated.  Thanks!
ryguy7272 - 11 Feb 2008 16:49 GMT
Sure, send me an email at:
ryguy7272@myhotmail.com

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.

Signature

RyGuy

> Hi Ryan.
>
[quoted text clipped - 53 lines]
> > > > > a bit of a novice when it comes to excel functions so any help would be
> > > > > greatly appreciated.  Thanks!
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.