Return multiple matches with multiple criteria as text

[gforgacs]

Hey, I have read some similar threads here and tried some of them but still no solution for me.

I have a large database where I would like to list all destinations of each driver for certain days. The main challenges are 1. any driver can appear in any of 3 columns, 2. there can be more than one matches. I have managed to use index; match formulas with IFNA logic to solve the first challenge, but cannot solve the second one to concat the multiple matches as it would work in a pivot table. Pivot table is not an option becase of the first challenge. Below is an example.

Data is in Sheet 1, expected result in Sheet 2 B2:C5

Sheet 1	A	B	C	D	E		Sheet 2	A	B	C
1​	Date	Driver 1	Driver 2	Driver 3	Destination		1​		2021.03.17​	2021.03.20​
2​	2021.03.17​	Driver A			Barcelona		2​	Driver A	Barcelona	Barcelona, Prague
3​	2021.03.17​	Driver B	Driver D		Wien		3​	Driver B	Wien	Wien
4​	2021.03.17​	Driver C			Budapest		4​	Driver C	Budapest	Budapest, Prague
5​	2021.03.20​	Driver A			Barcelona		5​	Driver D	Wien	Wien
6​	2021.03.20​	Driver B	Driver D		Wien
7​	2021.03.20​	Driver C			Budapest
8​	2021.03.20​	Driver A		Driver C	Prague

 

[Fluff]

How about

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H	I	J
1	Date	Driver 1	Driver 2	Driver 3	Destination				17/03/2021	20/03/2021
2	17/03/2021	Driver A			Barcelona			Driver A	Barcelona	Barcelona, Prague
3	17/03/2021	Driver B	Driver D		Wien			Driver B	Wien	Wien
4	17/03/2021	Driver C			Budapest			Driver C	Budapest	Budapest, Prague
5	20/03/2021	Driver A			Barcelona			Driver D	Wien	Wien
6	20/03/2021	Driver B	Driver D		Wien			Driver E
7	20/03/2021	Driver C			Budapest
8	20/03/2021	Driver A		Driver C	Prague
Sheet1

Cell Formulas
Range	Formula
I2:J6	I2	=TEXTJOIN(", ",,FILTER($E$2:$E$8,($A$2:$A$8=I$1)*(($B$2:$B$8=$H2)+($C$2:$C$8=$H2)+($D$2:$D$8=$H2)),""))

 

[gforgacs]

How about

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H	I	J
1	Date	Driver 1	Driver 2	Driver 3	Destination				17/03/2021	20/03/2021
2	17/03/2021	Driver A			Barcelona			Driver A	Barcelona	Barcelona, Prague
3	17/03/2021	Driver B	Driver D		Wien			Driver B	Wien	Wien
4	17/03/2021	Driver C			Budapest			Driver C	Budapest	Budapest, Prague
5	20/03/2021	Driver A			Barcelona			Driver D	Wien	Wien
6	20/03/2021	Driver B	Driver D		Wien			Driver E
7	20/03/2021	Driver C			Budapest
8	20/03/2021	Driver A		Driver C	Prague
Sheet1

Cell Formulas
Range	Formula
I2:J6	I2	=TEXTJOIN(", ",,FILTER($E$2:$E$8,($A$2:$A$8=I$1)*(($B$2:$B$8=$H2)+($C$2:$C$8=$H2)+($D$2:$D$8=$H2)),""))

WOW, Hats off! It works. =SZÖVEGÖSSZEFŰZÉS(", ";IGAZ;SZŰRŐ(Túranapló_heti!$H:$H;(Túranapló_heti!$D:$D='Sofőr check'!S$4)*((Túranapló_heti!$K:$K='Sofőr check'!$B14)+(Túranapló_heti!$L:$L='Sofőr check'!$B14)+(Túranapló_heti!$M:$M='Sofőr check'!$B14));""))

What if I would like to add another criteria, let's say, only filter if value in column F is "OK" or empty?

Sheet 1	A	B	C	D	E	F	Sheet 2	A	B	C
1​	Date	Driver 1	Driver 2	Driver 3	Destination	Status	1​		2021.03.17​	2021.03.20​
2​	2021.03.17​	Driver A			Barcelona	OK	2​	Driver A	Barcelona	Barcelona, Prague
3​	2021.03.17​	Driver B	Driver D		Wien	OK	3​	Driver B	Wien	Wien
4​	2021.03.17​	Driver C			Budapest		4​	Driver C	Budapest	Budapest, Prague
5​	2021.03.20​	Driver A			Barcelona	NA	5​	Driver D	Wien	Wien
6​	2021.03.20​	Driver B	Driver D		Wien	OK
7​	2021.03.20​	Driver C			Budapest	OK
8​	2021.03.20​	Driver A		Driver C	Prague

 

[Fluff]

How about

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H	I	J
1	Date	Driver 1	Driver 2	Driver 3	Destination	Status			17/03/2021	20/03/2021
2	17/03/2021	Driver A			Barcelona	OK		Driver A	Barcelona
3	17/03/2021	Driver B	Driver D		Wien	OK		Driver B	Wien	Wien
4	17/03/2021	Driver C			Budapest			Driver C		Budapest
5	20/03/2021	Driver A			Barcelona	NA		Driver D	Wien	Wien
6	20/03/2021	Driver B	Driver D		Wien	OK		Driver E
7	20/03/2021	Driver C			Budapest	OK
8	20/03/2021	Driver A		Driver C	Prague
Sheet1

Cell Formulas
Range	Formula
I2:J6	I2	=TEXTJOIN(", ",,FILTER($E$2:$E$8,($A$2:$A$8=I$1)*($F$2:$F$8="OK")*(($B$2:$B$8=$H2)+($C$2:$C$8=$H2)+($D$2:$D$8=$H2)),""))

 

[gforgacs]

How about

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H	I	J
1	Date	Driver 1	Driver 2	Driver 3	Destination	Status			17/03/2021	20/03/2021
2	17/03/2021	Driver A			Barcelona	OK		Driver A	Barcelona
3	17/03/2021	Driver B	Driver D		Wien	OK		Driver B	Wien	Wien
4	17/03/2021	Driver C			Budapest			Driver C		Budapest
5	20/03/2021	Driver A			Barcelona	NA		Driver D	Wien	Wien
6	20/03/2021	Driver B	Driver D		Wien	OK		Driver E
7	20/03/2021	Driver C			Budapest	OK
8	20/03/2021	Driver A		Driver C	Prague
Sheet1

Cell Formulas
Range	Formula
I2:J6	I2	=TEXTJOIN(", ",,FILTER($E$2:$E$8,($A$2:$A$8=I$1)*($F$2:$F$8="OK")*(($B$2:$B$8=$H2)+($C$2:$C$8=$H2)+($D$2:$D$8=$H2)),""))

Yep, yet again perfect, I think I've got the hinge of it. Thanks a lot!

 

[Fluff]

You're welcome & thanks for the feedback.