Rahul Mishra
Member
Dear sir how can i match criteria as given in excel sheet.
Criteria match
- Thread starter Rahul Mishra
- Start date
Khalid NGO
Excel Ninja
Hi Rahul,
Good day...
Try this in J3:
=IF(D2>=90%,INDEX($A$2:$D$15,MATCH(D2,$D$2:$D$15,0),1),"")
and this in K3:
=IF(D2<=60%,INDEX($A$2:$D$15,MATCH(D2,$D$2:$D$15,0),1),"")
Copy down both.
Regards,
Good day...
Try this in J3:
=IF(D2>=90%,INDEX($A$2:$D$15,MATCH(D2,$D$2:$D$15,0),1),"")
and this in K3:
=IF(D2<=60%,INDEX($A$2:$D$15,MATCH(D2,$D$2:$D$15,0),1),"")
Copy down both.
Regards,
Asheesh
Excel Ninja
Hi Rahul -
Try this in J3
IFERROR(INDEX($A$2:$A$15,MATCH(SMALL(IF($D$2:$D$15>=90%,ROW(A$1:$A$14),""),ROWS($A$1:A1)),IF($D$2:$D$15>=90%,ROW(A$1:$A$14),""),0)),"")
In K3 - IFERROR(INDEX($A$2:$A$15,MATCH(SMALL(IF($D$2:$D$15<=60%,ROW($A$1:B$14),""),ROWS($A$1:B1)),IF($D$2:$D$15<=60%,ROW($A$1:B$14),""),0)),"")
Drag them down as required..
Note: Please array enter them..
Try this in J3
IFERROR(INDEX($A$2:$A$15,MATCH(SMALL(IF($D$2:$D$15>=90%,ROW(A$1:$A$14),""),ROWS($A$1:A1)),IF($D$2:$D$15>=90%,ROW(A$1:$A$14),""),0)),"")
In K3 - IFERROR(INDEX($A$2:$A$15,MATCH(SMALL(IF($D$2:$D$15<=60%,ROW($A$1:B$14),""),ROWS($A$1:B1)),IF($D$2:$D$15<=60%,ROW($A$1:B$14),""),0)),"")
Drag them down as required..
Note: Please array enter them..
Khalid NGO
Excel Ninja
Dint notice that you guys replied....
Good day my friend Asheesh...
Dont worry we all learning something new by answering.
I always follow your ideas, so keep sharing.
Have a great weekend.
Ramesh Deo
Member
one more..
In cell J3:-
=INDEX($A$2:$A$15,SMALL(IF($D$2:$D$15>=90%,ROW($A$2:$A$15)-ROW($A$2)+1),ROW(J1)))
and in cell K3:-
=INDEX($A$2:$A$15,SMALL(IF($D$2:$D$15<=60%,ROW($A$2:$A$15)-ROW($A$2)+1),ROW(K1)))
Enter with Ctrl+Shift and drag down
In cell J3:-
=INDEX($A$2:$A$15,SMALL(IF($D$2:$D$15>=90%,ROW($A$2:$A$15)-ROW($A$2)+1),ROW(J1)))
and in cell K3:-
=INDEX($A$2:$A$15,SMALL(IF($D$2:$D$15<=60%,ROW($A$2:$A$15)-ROW($A$2)+1),ROW(K1)))
Enter with Ctrl+Shift and drag down