vlook up return more than one match

please help

i have a customer work book for outstanding orders of which there are hunderds of rows with there name order number and the items that are outstanding

when i have progressed all my purchased orders i take all the items and run it throught my work book using vlookup to return who wanted what items if any

but what if there are more then one customer wanting the same item how can i get vlookup to know that and return the next match


i'm still very new at excel so can someone please help me

attached is a small test of my work book

if you see item 3182Y/1.0/BLK
you can see it is wanted by both a.f switchgear and amg

but it will only return a.f switchgear

It seems that you are entering (pasting?) a list of items ordered in Col G, and you want to know who ordered that item. The problem is, what do you do when 2+ customers order the same item?

I suggest this different approach.
Sort your worksheet into order by item ordered, and you will automatically see when multiple customers have ordered it.

There may be other considerations that you haven't posted. So, if this doesn't work for you, say why.

i could do that but i have other fields that i pull through using vlook up like
customer
order number
qty wanted for each item
and date placed

and there are hundreds of orders and i may have only chase a few of the items

so vlookup pulls out the info that matches for the items i have chased


when it has returned the info i want i then copy the list created by vlookup and paste the values in a orders chased book for the sales staff

only having the items that have been chased with answers

vlookup works great but it's just this little hiccup

I still like sorting because you don't have to copy all of your row information to another place, and it avoids the problem of many customers for each part.

I have uploaded an approach based on added columns Lookup and Chased. They tag each row according to whether that row has a product that is being chased. You can then sort the rows to bring the desired ones to the top, for examination and copy.

Tell me if this helps.

Please Login or Register  to view this content.

I suggest that you do your analysis on a copy of the original workbook, leaving the original as part of your current system.

If you approach the problem by copying data and duplicates to another sheet, I predict a lot of complications. In a way, you will be copying the original workbook anyway, but as individual cells and rows, and using more complicated worksheet and macro functions.

I admit that those formulas make me dizzy as well. I'll leave it to someone else to analyze them. Good luck.

yes it does but i don't know how to break that code down to get it the work in my work book

there is a sample above

book 1 it's a sample of my work book


many thanks for you help

my work book looks like this

this is only a stripped down version i have it pull through more vlookup but they work on the order number which does not kick up a problem. it's just when matching the customer with their items

there can current 368 items outstanding and there are 23 duplicates
some more then twice

so i would like it to look through the list if there is only one then fine.
but if it finds one that has already returned a match to then look for the next

on the book 1

you will see item 3182Y/1.0/BLK
comes up twice
and it's wanted by both a.f switchgear and amg
but it only returns the first match a.f switchgear

Hi,

I dont know if this is what you exactly need however i gave it a try. I've notice that there are the same lookup values in column f like -> (3182Y/1.0/BLK) and i think that the VLOOKUP function is not capable of doing that. (please correct me if im wrong).

Try this formula on cell E2

combined IF, COUNTIF, INDEX & MATCH function.

Please Login or Register  to view this content.

I've attached a file.

That Works Great But How Do I Change It If There Are More Then 2 Matches

i being able to to take a copy of my work book


this is the template i use (taking out our info replacing it with a dummy customers)

its the order number i want to be the advanced lookup
as everything else works great


thank you for your help so far

BELOW IS THE CORRECT BOOK AS I POSTED THE WRONG ONE LAST TIME

Hi dphair1979,

Are you reffering your question to rylo or me?

Originally Posted by corinereyes

Hi dphair1979,

Are you reffering your question to rylo or me?

sorry
to yourself

i attached the wrong work book please have a look at the outstanding work book

hi all,

I tried solving this yesterday when there were fewer replies in the thread but I couldn't solve it using a formula only approach (& my head got dizzy too!) so I started trying different approaches (a macro could probably do it reasonably quickly but I'm not sure if that is wanted?).
A completely different approach to what has been considered so far is the use of a Pivot Table...
Does the attached file do what you are after?
Or can you change it to suit by dragging & dropping the fields?


btw, Debra has a great site for Pivot Table questions which are linked under P on the below page:

HTML Code: 

hth,
Rob

Hi dphair1979,

Are there any other unique ID in your database that can identify a particular Customer? If you dont mind can you attach a complete example of the data generated by your system.

Kindly look at the attached file. I've added a column for customer ID to correctly identify the Order No.

I will be glad to hear your feedback.

Hi dphair1979,

Here is the attachment.

I will be glad to have your feedback.

Originally Posted by rylo

Hi

F2: =IF(COUNTIF($G$2:G2,G2)>COUNTIF($C$2:$C$18,G2),"",INDEX(D:D,SMALL(IF($C$2:$C$18=G2,ROW($D$2:$D$18),""),COUNTIF($G$2:G2,G2))))

This formula is array entered using the ctrl, shift and enter keys.

Adjust the ranges as necessary, then copy down as required.


HTH

rylo

THANK YOU IT WORKS


THANK YOU FOR EVERYONE THAT HAS HELPED ME WITH THIS PROBLEM

ATTACHED IS THE WORKING BOOK

AGAIN THANK YOU