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

 

[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":

0	TEXT1
40	TEXT2
5	TEXT3
3	TEXT4
40	TEXT5
20	TEXT6
6	TEXT7

<tbody>
</tbody>

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...

Row\Col	A​	B​	C​	D​	E​	F​	G​
1​	Field-1	Field-2	Field-3	Field-4		AB
2​	AB	H1	0​	TEXT1		J5
3​	AB	J5	40​	TEXT2		3​
4​	AC	R5	5​	TEXT3		3​
5​	FG	H9	3​	TEXT4		Top N Scores	Top N values
6​	AB	J5	40​	TEXT5		40​	TEXT2
7​	AB	J5	20​	TEXT6		40​	TEXT5
8​	HZ	D5	6​	TEXT7		20​	TEXT6
9​

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.