Return all matches between two arrays

[Andrew-Mark]

Hi I have two lists of data that are of different lengths.
One is a dynamic range of between 1-10 lines and the other is a named range.

Is there a way to check the list in the table for any matches in the named range and return the cell value that matches?
There would be a maximum of two matches each time. I have already filtered a unique list of order numbers to another sheet.

Then (because I'm a little bit greedy) is there an easy way to add/concat a value from another cell in the same row to each result?
I have tried to give an example with a much reduced data sample. In reality I would be using a data set of c50,000 lines which I am splitting into about 10,000 ranges.

[Glenn Kennedy]

In J4, copied down:
=UNIQUE(B4:B19)

In K4, copied across and dnown:
=IFERROR(INDEX($C:$C,AGGREGATE(15,6,ROW($C$4:$C$19)/(($B$4:$B$19=$J4)*(ISNUMBER(MATCH($C$4:$C$19,$H$4:$H$12,0)))),COLUMNS($K4:K4))),"")

[Andrew-Mark]

Hi Glenn,

Many thanks for your help.
It took me a little while to work out how you did it :D
Didn't work for my workbook 'out of the box' but with a little tweaking it was perfect.

Could never have done it without your help

[Glenn Kennedy]

You're welcome.

---

It would be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.

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