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

 

[keresztesi]

One more question.

What if the "criteria 3" changes dynamically?
In my case - using your brilliant function - it gives me an empty cell.
For examle:
In cell "H5" it gives me the result I want, but in cell "H6" if the "criteria 3" changes it gives me an empty cell back.

Is there a way to handle it?

Thx
Zoli

 

[Aladin Akyurek]

One more question.

What if the "criteria 3" changes dynamically?
In my case - using your brilliant function - it gives me an empty cell.
For examle:
In cell "H5" it gives me the result I want, but in cell "H6" if the "criteria 3" changes it gives me an empty cell back.

Is there a way to handle it?

Thx
Zoli

If the criteria 3 changes the results will change accordingly.

 

[keresztesi]

If the criteria 3 changes the results will change accordingly.

Yes, it shold. Maybe it does, I don't understand it perfectly.

Here is what happens:

With criteria 1+2+3 in cell H5 comes good result
With criteria 1+2+3 in cell H6 comes good result BUT
with criteria 1+2+4 in cell H6 comes "empty" cell - if I change the function's end "...ROWS($H$5:H6))),"")" to "...ROWS($H$6:H6))),""), then
it gives me good result.

 

[Aladin Akyurek]

Yes, it shold. Maybe it does, I don't understand it perfectly.

Here is what happens:

With criteria 1+2+3 in cell H5 comes good result
With criteria 1+2+3 in cell H6 comes good result BUT
with criteria 1+2+4 in cell H6 comes "empty" cell - if I change the function's end "...ROWS($H$5:H6))),"")" to "...ROWS($H$6:H6))),""), then
it gives me good result.

What do you mean with criteria 1+2+4?

You need to understand that an output for AB, J5 and 40 is a specific set for these three criteria. If you would want an output for a different set of criteria separately, you need to extend the set up...

 

[keresztesi]

What do you mean with criteria 1+2+4?

You need to understand that an output for AB, J5 and 40 is a specific set for these three criteria. If you would want an output for a different set of criteria separately, you need to extend the set up...

Yes, I understand.
It is a bit hard to understand my needs

Ok, I try to make it more clear:
Please, take it so, that criteria 1+2 aren't changing, you can take them fix conditions.
Criteria 3 is so:
These are the values of column "C" and "D":
[TABLE="width: 128"]
<tbody>[TR]
[TD="class: xl63, width: 64"]0[/TD]
[TD="class: xl63, width: 64"]TEXT1[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64"]40[/TD]
[TD="class: xl63, width: 64"]TEXT2[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64"]5[/TD]
[TD="class: xl63, width: 64"]TEXT3[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64"]3[/TD]
[TD="class: xl63, width: 64"]TEXT4[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64"]40[/TD]
[TD="class: xl63, width: 64"]TEXT5[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64"]20[/TD]
[TD="class: xl63, width: 64"]TEXT6[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64"]6[/TD]
[TD="class: xl63, width: 64"]TEXT7[/TD]
[/TR]
</tbody>[/TABLE]

First result cell: it searches for max value (40) in column "C" and gives value of "D" back (TEXT2)
Second result cell: it searches for second largest value (40) in "C" and gives value of "D" back (TEXT5)
Third result cell: it searches for third largest value (20) in "C" and gives value of "D" back (TEXT6)

So, I'll need always three result cells with the 3 largest values from a range as criteria 3.

Is it more lear now?

Thx

 

[Aladin Akyurek]

This is a so-called conditional Top N question...

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][td]

F​

[/td][td]

G​

[/td][/tr][tr][td]

1​

[/td][td]Field-1[/td][td]Field-2[/td][td]Field-3[/td][td]Field-4[/td][td][/td][td]AB[/td][td][/td][/tr]
[tr][td]

2​

[/td][td]AB[/td][td]H1[/td][td]

0​

[/td][td]TEXT1[/td][td][/td][td]J5[/td][td][/td][/tr]
[tr][td]

3​

[/td][td]AB[/td][td]J5[/td][td]

40​

[/td][td]TEXT2[/td][td][/td][td]

3​

[/td][td][/td][/tr]
[tr][td]

4​

[/td][td]AC[/td][td]R5[/td][td]

5​

[/td][td]TEXT3[/td][td][/td][td]

3​

[/td][td][/td][/tr]
[tr][td]

5​

[/td][td]FG[/td][td]H9[/td][td]

3​

[/td][td]TEXT4[/td][td][/td][td]Top N Scores[/td][td]Top N values[/td][/tr]
[tr][td]

6​

[/td][td]AB[/td][td]J5[/td][td]

40​

[/td][td]TEXT5[/td][td][/td][td]

40​

[/td][td]TEXT2[/td][/tr]
[tr][td]

7​

[/td][td]AB[/td][td]J5[/td][td]

20​

[/td][td]TEXT6[/td][td][/td][td]

40​

[/td][td]TEXT5[/td][/tr]
[tr][td]

8​

[/td][td]HZ[/td][td]D5[/td][td]

6​

[/td][td]TEXT7[/td][td][/td][td]

20​

[/td][td]TEXT6[/td][/tr]
[tr][td]

9​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

F3: 3 (a user-entered Top N value)

In F4 control+shift+enter, not just enter:

=SUM(IF(A2:A8=F1,IF(B2:B8=F2,IF(C2:C8>=LARGE(IF(A2:A8=F1,IF(B2:B8=F2,C2:C8)),MIN(F3,SUM(IF(A2:A8=F1,IF(B2:B8=F2,IF(ISNUMBER(C2:C8),1)))))),1))))

This adjusts the user-entered Top N value if necessary (i.e. as the data dictates).

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

=IF(ROWS($F$6:F6)>$F$4,"",LARGE(IF($A$2:$A$8=$F$1,IF($B$2:$B$8=$F$2,$C$2:$C$8)),ROWS($F$6:F6)))

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

=IF($F6="","",INDEX($D$2:$D$8,SMALL(IF($A$2:$A$8=$F$1,IF($B$2:$B$8=$F$2,IF($C$2:$C$8=$F6,ROW($D$2:$D$8)-ROW($D$2)+1))),COUNTIFS($F$6:F6,F6))))

 

[keresztesi]

Thanks a lot, Aladin Akyurek!!!
It works!!! :D

YOU ARE A LEGEND!

 

[Aladin Akyurek]

You are welcome.