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.
<tbody>
</tbody>
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,
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.
EVENT | NAMES | TYPE |
2 | ABC | HARD |
2 | CDE | SOFT |
1 | EFG | HARD |
3 | HIJ | MED |
1 | KLM | MED |
2 | NOP | HARD |
3 | QRS | HARD |
2 | TUV | SOFT |
2 | WXY | HARD |
<tbody>
</tbody>
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,