Help with an Index Match formula with multiple criteria AND requiring multiple results

[willow1985]

I have this formula:

{=INDEX(CAPA!A:A,MATCH(1,(B17=CAPA!V:V)*(Dashboard!W1=CAPA!D:D),0))}

And what it does is it performs an index match with multiple criteria:
if the value in Column V (on sheet CAPA) matches B17 AND
if the value in column D (on sheet CAPA) matches W1 (on sheet Dashboard)
It will return the value in column AA (from sheet CAPA)

Now it will return the first match value, example: Blue 892

However I would like it to return ALL matches in Joint text: Blue 892, Green 596, Yellow 555 etc.

Is this possible?

Thank you to anyone who can help


Carla

 

[tyija1995]

Hey there,

Is this something you're looking for?

Book1
	A	B	C	D	E	F
1	blue 892	flag	other flag		flag	blue 892, green 596, yellow 555
2	green 596	flag	other flag		other flag
3	red 88	no flag	no flag
4	yellow 555	flag	other flag
Sheet1

Cell Formulas
Range	Formula
F1	F1	=TEXTJOIN(", ",TRUE,INDEX($A$1:$A$4,SMALL(IF($B$1:$B$4=$E$1,IF($C$1:$C$4=$E$2,ROW($A$1:$A$4))),ROW(INDIRECT("$1:$"&SUMPRODUCT((B1:B4=E1)*(C1:C4=E2)))))))

You'll need to change the criteria parts however, I used E1 and E2 in the example as criteria 1 & 2

 

[Fluff]

Another option
=TEXTJOIN(", ",1,IF((Capa!V1:V200=B17)*(Capa!D1:D200=W1),Capa!A1:A200,""))

Needs array entry

 

[willow1985]

The formula did not work.
I changed the criteria in your formula to reference the cells I needed referenced, however it gave me all of the data in Column A.

=TEXTJOIN(", ",TRUE,INDEX(CAPA!A:A,SMALL(IF(CAPA!V:V=B17,IF(CAPA!D:D=Dashboard!W1,ROW(CAPA!A:A))),ROW(INDIRECT("$1:$"&SUMPRODUCT((CAPA!V:V=B17)*( CAPA!D:D=Dashboard!W1)))))))

I also recreated your spreadsheet and used the exact formula you provided and only got a result of blue 892

 

[willow1985]

Fluff yours works perfectly.

Thank you both for your help ?

 

[Fluff]

Glad we could help & thanks for the feedback

 

[Fluff]

I also recreated your spreadsheet and used the exact formula you provided and only got a result of blue 892

You don't actually need to to that, one of the beauties about the XL2BB add-in is you can click the copy icon

and then just paste it into Excel.
It's one of the reasons we ask people to use it.

 

[tyija1995]

Hey, apologies that my solution did not work, I think it is because of the office version difference

 

[willow1985]

Hey, apologies that my solution did not work, I think it is because of the office version difference

No worries, thank you again. I appreciate any and all help