Extract values from range that match 2 criteria and return results in single cell

[Dave_P_C]

I have a table of around 1000 rows and 25 columns from which I want to query on 2 parameters and return the results in a single cell.

The table below is simplified to show what I am trying to achieve but essentially I am trying to create a query (without VBA if possible) that will lookup all the EVENTS of a given number with a TYPE of hard and return all the NAMES in a single cell. For example, the results of querying against EVENT=2 and TYPE=HARD would return ABC, NOP, WXY.

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD="align: center"]EVENT[/TD]
[TD="align: center"]NAMES[/TD]
[TD="align: center"]TYPE[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]ABC[/TD]
[TD]HARD[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]CDE[/TD]
[TD]SOFT[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]EFG[/TD]
[TD]HARD[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]HIJ[/TD]
[TD]MED[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]KLM[/TD]
[TD]MED[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]NOP[/TD]
[TD]HARD[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]QRS[/TD]
[TD]HARD[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]TUV[/TD]
[TD]SOFT[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]WXY[/TD]
[TD]HARD[/TD]
[/TR]
</tbody>[/TABLE]

I have played around with TEXTJOIN and INDEX MATCH queries but not worked out a solution.

{=TEXTJOIN(", ",TRUE,IF(event=2,name,""))} works if I wanted to have all the EVENT 2 names but if I try to add a second filter for TYPE it does not work

{=TEXTJOIN(", ",TRUE,IF(event=2,IF(type="HARD",name,"")))}

Can this be achieved with TEXTJOIN or would it need some other query?

Thanks,

 

[wideboydixon]

Smaller set and I'm not sure how it would work with the larger data set but:

Book1
	A	B	C	D	E
1	EVENT	NAMES	TYPE		ABC,NOP,WXY
2	2	ABC	HARD
3	2	CDE	SOFT
4	1	EFG	HARD
5	3	HIJ	MED
6	1	KLM	MED
7	2	NOP	HARD
8	3	QRS	HARD
9	2	TUV	SOFT
10	2	WXY	HARD
Sheet1

Cell Formulas
Range	Formula
E1	{=TEXTJOIN(",",TRUE,IF($A$2:$A$10=2,IF($C$2:$C$10="HARD",$B$2:$B$10,""),""))}
Press CTRL+SHIFT+ENTER to enter array formulas.

WBD

 

[Dave_P_C]

Thank You WBD,

Your formula works on the larger data set as well.

I see part of the the issue with mine was trying to use names for ranges rather than cell / column references.