INDEX MATCH with multiple matches

[Pickle1989]

I've been working on this for a while now without any luck, so I thought it might be time I asked the experts.

I have 2 workbooks:
Workbook 1: Raw postcode data only in column A


Workbook 2 (Index): Postcodes (column A) with all localities affiliated with those postcodes (Column B). Note that some postcodes are used for multiple localities.


I have created the Index & Match formula for column B to bring over the localities of postcodes in workbook 2 to workbook 1, but it will only bring over one locality (see below)


=INDEX([AUpostcodesJune2021.csv]VIC!$A$1:$C$3532,MATCH(A2,[AUpostcodesJune2021.csv]VIC!$A$1:$A$3532,0),MATCH($B$1,[AUpostcodesJune2021.csv]VIC!$A$1:$C$1,0))

What should I be adding to my formula to get the multiple localities for a postcode brought over from my Index and have them separated by comma, ie: 3011 - Footscray, Seddon, Seddon West

Any help would be greatly appreciated

 

[Peter_SSs]

Welcome to the MrExcel board!

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

See if you can adapt from this single-sheet example.

Pickle 1989.xlsm
	A	B	C	D	E	F
1	postcode	locality			postcode	locality
2	3008	Loc 1			3008	Loc 1
3	3010	Loc 3			3009	Loc 2
4	3011	Loc 5			3010	Loc 3
5	3011	Loc 6			3010	Loc 4
6	3012	Loc 9			3011	Loc 5
7	3015	Loc 14			3011	Loc 6
8	3012	Loc 10			3011	Loc 7
9					3011	Loc 8
10					3012	Loc 9
11					3012	Loc 10
12					3012	Loc 11
13					3013	Loc 12
14					3014	Loc 13
15					3015	Loc 14
Sheet1

Cell Formulas
Range	Formula
B2:B8	B2	=INDEX(FILTER(F$2:F$15,E$2:E$15=A2),COUNTIF(A$2:A2,A2))