Index with one condition and an OR conditon between 2

[as9178]

Hi,

Had some help on this spreadsheet a while back but now its expanded.

So the problem is this

On the EM Ranking tab I need E3 to pull through the player numbers from the triallists tab.

The array formula on E3 currently reads as this

=IFERROR(INDEX(Trialists!$E$2:$E$44, SMALL(IF((E$2=Trialists!$B$2:$B$44)*(Trialists!$A$2:$A$44="Yes"), ROW(Trialists!$E$2:$E$44)-1,""), ROW()-2)),"")

which works fine.

The OR condition I need now that is considered E$2=Trialists!$B$2:$B$44 or (Trialists!$C$2:$C$44="Yes")

So the first one for E2 is whether B2:B44 = EM on the triallist tab

or

Whether C2:C44 = Yes (they wouldnt have EM associated with it)

I will still need (Trialists!$A$2:$A$44="Yes") in the formula

Hope that makes sense?

Thanks in advance

Andrew

[AliGW]

Perhaps try this:

=IFERROR(INDEX(Trialists!$E$2:$E$44, SMALL(IF(((E$2=Trialists!$B$2:$B$44)+(Trialists!$C$2:$C$44="Yes"))*(Trialists!$A$2:$A$44="Yes"), ROW(Trialists!$E$2:$E$44)-1,""), ROW()-2)),"")

[as9178]

That brings through the players who are U24M category but also Elite.

It doesnt bring through the players wo are in category EM

These are the numbers I would expect

81
86
87
92
96
165
166
167
168
170
171
172
173
174
175
176
177
178
179
180
181
182
183

[AliGW]

Well, none of the EM are YES in the adjacent column. You said this:

I will still need (Trialists!$A$2:$A$44="Yes") in the formula

So maybe a bit more clarity?

[AliGW]

Maybe you mean this:

=IFERROR(INDEX(Trialists!$E$2:$E$44, SMALL(IF((E$2=Trialists!$B$2:$B$44)+(Trialists!$A$2:$A$44="Yes"), ROW(Trialists!$E$2:$E$44)-1,""), ROW()-2)),"")

[as9178]

Maybe you mean this:

=IFERROR(INDEX(Trialists!$E$2:$E$44, SMALL(IF((E$2=Trialists!$B$2:$B$44)+(Trialists!$A$2:$A$44="Yes"), ROW(Trialists!$E$2:$E$44)-1,""), ROW()-2)),"")

Hi Ali, the criteria I need to meet is the following

Tab - Triallists
A2 and down = Yes
B2 and down = EM
plus
C2 and down = Yes

That should provide the numbers required

[AliGW]

So you need all criteria meeting? If so, it’s not an OR match, it’s an AND match.

=IFERROR(INDEX(Trialists!$E$2:$E$44, SMALL(IF((E$2=Trialists!$B$2:$B$44)*(Trialists!$A$2:$A$44="Yes") *(Trialists!$c$2:$C$44="Yes"),ROW(Trialists!$E$2:$E$44)-1,""), ROW()-2)),"")

[as9178]

So you need all criteria meeting? If so, it’s not an OR match, it’s an AND match.

=IFERROR(INDEX(Trialists!$E$2:$E$44, SMALL(IF((E$2=Trialists!$B$2:$B$44)*(Trialists!$A$2:$A$44="Yes") *(Trialists!$c$2:$C$44="Yes"),ROW(Trialists!$E$2:$E$44)-1,""), ROW()-2)),"")

Not quite, If its EM the number is returned. If it has a Yes against the Elite column that number is also returned.

[Fluff13]

Is this what you mean?
=IFERROR(INDEX(Trialists!$E$2:$E$44,AGGREGATE(15,6,(ROW(Trialists!$E$2:$E$44)-ROW(Trialists!$E$2)+1)/((Trialists!$B$2:$B$44=$E$2)+((Trialists!$A$2:$A$44="Yes")*(Trialists!$C$2:$C$44="Yes"))),ROWS(E$3:E3))),"")

B=EM or A & C =Yes

[AliGW]

Sorry - I’m not getting what you want.

[AliGW]

Administrative Note:

Please don't quote whole posts, especially when you are responding to the one immediately preceding your own - it's just clutter. It's OK to quote if you are responding to a post out of sequence, but limit quoted content to a few relevant lines that makes clear to whom and what you are responding. Thanks!

For normal conversational replies, try using the QUICK REPLY box below or the REPLY button instead of REPLY WITH QUOTE.

[as9178]

Is this what you mean?
=IFERROR(INDEX(Trialists!$E$2:$E$44,AGGREGATE(15,6,(ROW(Trialists!$E$2:$E$44)-ROW(Trialists!$E$2)+1)/((Trialists!$B$2:$B$44=$E$2)+((Trialists!$A$2:$A$44="Yes")*(Trialists!$C$2:$C$44="Yes"))),ROWS(E$3:E3))),"")

B=EM or A & C =Yes

Thats the one. That formula is so far above my level its untrue

[Fluff13]

Glad we could help & thanks for the feedback.

[as9178]

Fluff13, Ive just realsied I need one more condition added.

I also need G2 and below to equal M

Can you rejig the formula please?

B=EM or A + 'G = M' & C =Yes

[Fluff13]

How about
=IFERROR(INDEX(Trialists!$E$2:$E$44,AGGREGATE(15,6,(ROW(Trialists!$E$2:$E$44)-ROW(Trialists!$E$2)+1)/((Trialists!$B$2:$B$44=$E$2)+((Trialists!$G$2:$G$44="M")*(Trialists!$A$2:$A$44="Yes")*(Trialists!$C$2:$C$44="Yes"))),ROWS(E$3:E3))),"")

Also please read post#11

[as9178]

Hi Fluff, that isnt returning the correct result. Its pulling through 71 as the first number from column G = M

Edit: Me being stupid

[Fluff13]

Not for me it doesn't, I get.

Excel 2016 (Windows) 32 bit

	E	F
2	EM	Beep Test
3	73	0.0
4	77	27.0
5	81	28.0
6	86	28.0
7	87	23.0
8	92	23.0
9	96	26.0
10	165	0.0
11	166	0.0
12	167	0.0
13	168	0.0
14	170	0.0
15	171	0.0
16	172	0.0
17	173	0.0
18	174	0.0
19	175	0.0
20	176	0.0
21	177	0.0
22	178	0.0
23	179	0.0
24	180	0.0
25	181	0.0
26	182	0.0
27	183	0.0

Sheet: EM Rankings

[AliGW]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

A word of thanks might be nice, too ...

[as9178]

Thankyou veyr much for all your help

[Fluff13]

Glad we could help & thanks for the feedback.