Index / Match all results with multiple criteria

[keresztesi]

Hi,

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

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[/TR]
[TR]
[TD]AB[/TD]
[TD]H1[/TD]
[TD]0[/TD]
[TD]TEXT1[/TD]
[TD][/TD]
[TD]CRITERIA1[/TD]
[TD]A=[/TD]
[TD]AB[/TD]
[/TR]
[TR]
[TD]AB[/TD]
[TD]J5[/TD]
[TD]40[/TD]
[TD]TEXT2[/TD]
[TD][/TD]
[TD]CRITERIA2[/TD]
[TD]B=[/TD]
[TD]J5[/TD]
[/TR]
[TR]
[TD]AC[/TD]
[TD]R5[/TD]
[TD]5[/TD]
[TD]TEXT3[/TD]
[TD][/TD]
[TD]CRITERIA3[/TD]
[TD]C=[/TD]
[TD]40[/TD]
[/TR]
[TR]
[TD]FG[/TD]
[TD]H9[/TD]
[TD]3[/TD]
[TD]TEXT4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AB[/TD]
[TD]J5[/TD]
[TD]40[/TD]
[TD]TEXT5[/TD]
[TD][/TD]
[TD]MATCHES=[/TD]
[TD]TEXT2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AB[/TD]
[TD]J5[/TD]
[TD]20[/TD]
[TD]TEXT6[/TD]
[TD][/TD]
[TD][/TD]
[TD]TEXT5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]HZ[/TD]
[TD]D5[/TD]
[TD]6[/TD]
[TD]TEXT7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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.