Index / Match all results with multiple criteria

[keresztesi]

Hi,

I cannot find a solution for my problem.
Is it like this:

A	B	C	D	E	F	G	H
AB	H1	0	TEXT1		CRITERIA1	A=	AB
AB	J5	40	TEXT2		CRITERIA2	B=	J5
AC	R5	5	TEXT3		CRITERIA3	C=	40
FG	H9	3	TEXT4
AB	J5	40	TEXT5		MATCHES=	TEXT2
AB	J5	20	TEXT6			TEXT5
HZ	D5	6	TEXT7

<tbody>
</tbody>

Can anyone help me, please?

Thx,
Zoli

 

[Scott T]

This is an array formula and must be committed with CONTROL+SHIFT+ENTER. If done correctly Excel will put {} around the formula. Copy down

=IF(ROWS($H$5:H5)>COUNTIFS($A$1:$A$7,$H$1,$B$1:$B$7,$H$2,$C$1:$C$7,$H$3),"",INDEX($D$1:$D$7,SMALL(IF($H$1=$A$1:$A$7,IF($H$2=$B$1:$B$7,IF($H$3=$C$1:$C$7,ROW($D$1:$D$7)))),ROWS($H$5:H5))))

 

[keresztesi]

Thx, but it doesn't work for me.
Is it maybe possible that I send my excel file to you and you write the code inside it?

Thx

 

[Scott T]

How does it not work? Did you use Ctrl+Shift+Enter?

 

[Aladin Akyurek]

In H5 control+shift+enter, not just enter, and copy down:

=IFERROR(INDEX($D$1:$D$7,SMALL(IF($A$1:$A$7=$H$1,IF($B$1:$B$7=$H$2,IF($C$1:$C$7=$H$3,ROW($D$1:$D$7)-ROW($D$1)+1))),ROWS($H$5:H5))),"")

which is robust against row insertions in front of the current header row.

See: Dropbox - keresztesi multiple criteria sublist.xlsx

 

[keresztesi]

Yes, I used control+shift+enter, but it dowsn't work for me.
Now, it gives an empty cell back.

The sample above in my file looks like this:
{=IFERROR(INDEX(breakdown!$A$2:$A$540;SMALL(IF(breakdown!B2:B540=DashBoard!A3;IF(breakdown!C2:C540=DashBoard!A20;IF(breakdown!D2:D540=DashBoard!C21;ROW(breakdown!$A$2:$A$540)+1)));ROW(DashBoard!$E$21:DashBoard!E21)));"")}

Any suggestions?

 

[Aladin Akyurek]

...
Any suggestions?

See the workbook of post #5 which you can adapt.

 

[keresztesi]

Yes, I see it works in that file.
Am I maybe missing some other criteria? I don't know.

Here is my file, I used purple for the important cells.
Dropbox - idontbelieveit.xlsm

Can you please check it? You can disable macro if you like.

Thx

 

[keresztesi]

FINALLY!!!! Thank you for your help! The attached file saved me!
It was (who else's) my mistake ... I wrote rows everywhere.

Ok, now it's done!

Thank you again!

Zoli

 

[Aladin Akyurek]

FINALLY!!!! Thank you for your help! The attached file saved me!
It was (who else's) my mistake ... I wrote rows everywhere.

Ok, now it's done!

Thank you again!

Zoli

Great that that helped you out. Thanks for the update.