Search from multi range

[TH123]

Hi Excel master,

I've data source as same as peach area & What I need to do is yellow area (uploaded image). Pls support. Thanks.

Data Source

Item	Location	Item type
a,b,c	North	Cap
b,d,a	South	Hat
b,a	North	Cap

Need to insert formula at "item type"column

Item	Location	Item type
a	North	Which formula should be used to see result = cap?
a	South	Which formula should be used to see result = hat?
b	North	Which formula should be used to see result = cap?
b	South	Which formula should be used to see result = hat?

 

[Peter_SSs]

I am assuming that in your actual data, the "Items" are not all just single letters like your sample. See if this does what you want.

23 05 30.xlsm
	A	B	C
1	Item	Location	Item type
2	a,b,c	North	Cap
3	b,d,a	South	Hat
4	b,a	North	Cap
5
6	Item	Location	Item type
7	a	North	Cap
8	a	South	Hat
9	b	North	Cap
10	b	South	Hat
Lookup

Cell Formulas
Range	Formula
C7:C10	C7	=UNIQUE(FILTER(C$2:C$4,ISNUMBER(SEARCH(","&A7&",",","&A$2:A$4&","))*(B$2:B$4=B7),""))

 

[TH123]

I am assuming that in your actual data, the "Items" are not all just single letters like your sample. See if this does what you want.

23 05 30.xlsm
	A	B	C
1	Item	Location	Item type
2	a,b,c	North	Cap
3	b,d,a	South	Hat
4	b,a	North	Cap
5
6	Item	Location	Item type
7	a	North	Cap
8	a	South	Hat
9	b	North	Cap
10	b	South	Hat
Lookup

Cell Formulas
Range	Formula
C7:C10	C7	=UNIQUE(FILTER(C$2:C$4,ISNUMBER(SEARCH(","&A7&",",","&A$2:A$4&","))*(B$2:B$4=B7),""))

Hi Peter,

Sorry for late response. Yes, my actual data is not single letter & I've tried with your formula & it works well. Appriciated your support with full scenarios of data

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.