Return Multiple Values From Two Columns In Two Separate Workbooks

[DPXCV]

I have two workbooks that I am trying to match up multiple of the same sales orders numbers to different item numbers that fall under the same sales order number.

Column B in both workbooks contain the SO number, I would like to have a formula in column C of the second workbook that returns all the different item numbers in the first book, which is list under column C (see below).

Workbook 1 Workbook2

 

[dreid1011]

Filter may work for you:

FILTER function - Microsoft Support

How to use the FILTER function in Excel to filter a range of data based on criteria you define.

support.microsoft.com

 

[DPXCV]

Filter may work for you:

FILTER function - Microsoft Support

How to use the FILTER function in Excel to filter a range of data based on criteria you define.

support.microsoft.com

I would have to copy/paste for each different SO number and, if there's more items in workbook1 for an SO than line items for an SO in workbook two, it would give me a spill error. Hoping for some type of Index/Match formula, but I haven't been able to craft one.

 

[LegendaryAxe]

I would have to copy/paste for each different SO number and, if there's more items in workbook1 for an SO than line items for an SO in workbook two, it would give me a spill error. Hoping for some type of Index/Match formula, but I haven't been able to craft one.

Why don't you wrap it in the TEXTJOIN function? That way you can have multiple items appear in one cell. If you don't want duplicates wrap the filter function in the UNIQUE function.

 

[DPXCV]

Why don't you wrap it in the TEXTJOIN function? That way you can have multiple items appear in one cell. If you don't want duplicates wrap the filter function in the UNIQUE function.

I am looking to do a line by line comparison so unfortunately that would not be very practical. dreid1011's solution of a filter function is working well, just a bit tedious as I have to copy that formula for each new SO. There's another option that appeared in the Similar Threads below where a solution was offered for a similar question, but it puts all copies in new columns on the same row; again, not ideal, but effective.

Outside of filter spills, I don't believe that functionality exists, at least with out some form of macros involved.

VLOOKUP on one set of criteria and return multiple matches in a row?

I have two reports, one that has aged inventory data and the other that has sales data with inventory items on it. What I am trying to do is look up the item number from the aged inventory report and then go to the sales report, find that same item number and return all the customer names that...

www.mrexcel.com

 

[Fluff]

You could use the filter function & wrap it in CHOOSEROWS, something like

=CHOOSEROWS(FILTER(Sheet1!$C$2:$C$100,Sheet1!$B$2:$B$100=B2),COUNTIFS(B$2:B2,B2))

 

[DPXCV]

You could use the filter function & wrap it in CHOOSEROWS, something like

=CHOOSEROWS(FILTER(Sheet1!$C$2:$C$100,Sheet1!$B$2:$B$100=B2),COUNTIFS(B$2:B2,B2))

That did the trick! Now I need to include another column, how would it be modified? Now I need to bring pricing over to match both the SO number and the item number.

 

[Fluff]

How about

=CHOOSEROWS(FILTER(Sheet1!$C$2:$D$100,Sheet1!$B$2:$B$100=B2),COUNTIFS(B$2:B2,B2))

which will return both columns

 

[DPXCV]

How about

=CHOOSEROWS(FILTER(Sheet1!$C$2:$D$100,Sheet1!$B$2:$B$100=B2),COUNTIFS(B$2:B2,B2))

which will return both columns

Very nice, thanks Fluff!

 

[Fluff]

You're welcome & thanks for the feedback.