How to lookup multiple items from one tab, multiple rows from another tab then insert

[patxlb]

I have a speadsheet that I need to do the following:

Tab One, called NM Parts, has a list of master item number
Tab Two, called Routings, has a list of item numbers with multiple rows per item number

I want to be able to create a third tab which will contain all the item numbers from tab one with the multiple rows for the item number from tab two (if found) into tab three.

Is this possible?

[dosydos]

upon a quick review of your Routings tab, you have duplicate values in your ITEM NO column. What happens if one of these duplicate values is on your NM Parts Tab? How do you want this data shown on the third Tab?

[Glenn Kennedy]

Hahaha. that had me worried. My formula returned No results. It took a while to discover why. There were NO matching item Nos between the two lists. I added some.

In A2, coipied across and dnown:

=IFERROR(INDEX(Routings!A:A,AGGREGATE(15,6,ROW(Routings!$A$2:$A$50)/(ISNUMBER(MATCH(Routings!$A$2:$A$50,'NM Parts'!$A$2:$A$11,0))),ROWS($1:1))),"")

[Ace_XL]

If you have a large data set, use a helper column (Col J) in the routings sheet

=COUNTIF('NM Parts'!A:A,A2)

Then in a new sheet pull up the non-zero values

=IF(ROWS($A$1:$A1)>COUNTIF(Routings!$J$2:$J$1000,">"&0),"",INDEX(Routings!A$2:A$1000,SMALL(INDEX((Routings!$J$2:$J$1000>0)*(ROW(Routings!$J$2:$J$1000)-ROW($J$2)+1),),COUNTIF(Routings!$J$2:$J$1000,"<="&0)+COUNTBLANK(Routings!$J$2:$J$1000)+ROWS($A$1:$A1))))

Copied down and across
how-to-lookup-multiple-items-from-one-tab-multiple-rows-from-another-tab-then-insert-routings-t.xlsx

[patxlb]

Thank you everyone for all your help. Merry Christmas

[Glenn Kennedy]

You're welcome.

---

If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.

[patxlb]

I thought I had it but I'm still having problems. I have more parts in the "combined" tab than are in the "NM Parts" tab. Also at least one part is missing.

I have attached the original file for your review.

[Glenn Kennedy]

Did you actually try my formula? It returns 885 results. the correct number.

[patxlb]

Yes, I used your formula and thank you for the assistance. My question is why does the tab "combined" contain parts that were not on the NM Parts tab. One example is located on row 2 (item number 40048700); another is row 6 (item number 40050520)

Thank you.

[Glenn Kennedy]

Neither of them are present on the Combined sheet, if you use my formula. Mind you, it would help if I had attached the correct sheet at my last post.!!!!

[patxlb]

Thank you very much. You are a life-saver.

[Glenn Kennedy]

You're welcome and thanks for the rep.