Is this even possible, with INDEX MATCH

[Baba_Yaga]

Hello, Im lost with this. even my IT Department is having trouble.
I have a list of parts.
List 1 has single part number, description and total qty.
well list 2 comes from another department and may have 1 part number
with multiple delivery locations.

My department would like to be able to use index/array/match or a combination of them
to match part number on list one to part number on list 2 and return the delivery location(s)

Thanks for your help

 

[sheetspread]

See here:

https://www.mrexcel.com/forum/excel...-all-matches-column-up-corresponding-row.html

 

[Aladin Akyurek]

Let A:C of Sheet1 house "List 1".

Let A:B of Sheet2 house "List 2".

In D2 of Sheet1, control+shift+enter, not just enter, copy across:

=IFERROR(INDEX(Sheet2!$B$2:$B$100,SMALL(IF(Sheet2!$A$2:$A$100=$A2,ROW(Sheet2!$B$2:$B$100)-ROW(Sheet2!$B$2)+1),COLUMNS($D2:D2))),"")