INDEX/MATCH with (a) Duplicates (b) based on criteria of another cell being a number

[SleightOfHand]

Hi, I've been trying do this for a while but I'm a bit stuck.

Book1.xlsx
	E	F	H
1	Code	Name	Number
2
3	1A91	Area 1	95
4	1A91	Area 2	95
5	1A91	Area 3
6	1A91	Area 4
7	1A91	Area 5
8	1A92	Area 1
9	1A92	Area 2
10	1A92	Area 3	91
11	1A92	Area 4	92
12	1A92	Area 5
13
Sheet1

There feels like it should be a much simpler way to do this but I cannot seem to look logically at this.

As you can see, column A has a list of multiple codes that may correspond to a certain area. What I want to do is, separately, be able to extract which area(s) correspond to the codes, which I filtered out in another column. But in order to filter these out, they must correspond to a the number column. So if there is a column with whatever code, I can list out all the areas that correspond to the code, that have a number.

Book1.xlsx
	A	B	C	D
15	1A91	Area 1	Area 2
16	1A92	Area 3	Area 4
Sheet1

So in another column, I used the UNIQUE command just to filter out all the duplicates within the first column, and I'd like to be able to use XLOOKUP or INDEX/MATCH to be able to extract out all these areas which have a number corresponding to the code.


I've tried an INDEX/SMALL/ROW function, which works to filter out all the areas that have a number, but not how it would correspond to the lookup 'code'. So it would great to have it like the way it's formatted just above.

 

[Fluff]

How about

+Fluff New.xlsm
	E	F	G	H	I	J	K
1	Code	Name	Number
2					1A91	Area 1	Area 2
3	1A91	Area 1	95		1A92	Area 3	Area 4
4	1A91	Area 2	95
5	1A91	Area 3
6	1A91	Area 4
7	1A91	Area 5
8	1A92	Area 1
9	1A92	Area 2
10	1A92	Area 3	91
11	1A92	Area 4	92
12	1A92	Area 5
Master

Cell Formulas
Range	Formula
J2:K3	J2	=TRANSPOSE(FILTER($F$3:$F$12,($E$3:$E$12=I2)*($G$3:$G$12<>"")))
Dynamic array formulas.

 

[SleightOfHand]

Thanks so much - it worked

 

[Fluff]

You're welcome & thanks for the feedback.