Multiple criteria index/small

[peterpaul]

Hi everyone,

I have made a little example to explain my question. I would like to get the scores from names A and B. That part is working in my example, but I would like to add 2 more criteria's. I would like to get the scores of names A and B but the score can't contain 5 and the factor should be bigger than 0. Is this possible? If so, how do I had these 2 new criteria's?

excel.xlsx
	A	B	C	D	E	F	G
1	Name	Score	Factor		Criteria
2	A	1	1		A		1
3	B	2	0		B		2
4	C	3	1		5		5
5	C	4	1				6
6	A	5	1				8
7	B	6	1				#GETAL!
8	C	7	1				#GETAL!
9	A	8	1				#GETAL!
Blad1

Cell Formulas
Range	Formula
G2:G9	G2	=INDEX($B$2:$B$9,SMALL(IF((($E$2=$A$2:$A$9)+($E$3=$A$2:$A$9))>0, ROW($A$2:$A$9)-ROW($A$2)+1),ROWS(E$2:E2)))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Fluff]

Hi & welcome to MrExcel.
How about

+Fluff 1.xlsm
	A	B	C	D	E	F	G
1	Name	Score	Factor		Criteria
2	A	1	1		A		1
3	B	2	0		B		6
4	C	3	1		5		8
5	C	4	1				#NUM!
6	A	5	1				#NUM!
7	B	6	1				#NUM!
8	C	7	1				#NUM!
9	A	8	1				#NUM!
10
Main

Cell Formulas
Range	Formula
G2:G9	G2	=INDEX($B$2:$B$9,AGGREGATE(15,6,(ROW($A$2:$A$9)-ROW($A$2)+1)/((($A$2:$A$9=$E$2)+($A$2:$A$9=$E$3))>0)/($B$2:$B$9<>$E$4)/($C$2:$C$9>0),ROWS(E$2:E2)))

 

[peterpaul]

It works like a charm thank you very much. I have one question left. Is it possible that the formula doesn't produce any duplicates? So when I have an A with score 6 and a B with score 6 the formula only outputs 6 once? So with other words that all outcomes are unique.

 

[Fluff]

How about

=INDEX($B$2:$B$9,AGGREGATE(15,6,(ROW($A$2:$A$9)-ROW($A$2)+1)/((($A$2:$A$9=$E$2)+($A$2:$A$9=$E$3))>0)/($B$2:$B$9<>$E$4)/($C$2:$C$9>0)/(ISNA(MATCH($B$2:$B$9,G$1:G1,0))),1))