Forum Discussion
Solved
Need help: index multiple results based on multiple criteria
Hi all,
I am struggling with achieving the following result:
As you can see in N8 I have the formula to give me the employee name, based on 3 criterias, however I want to adjust the formula, so that when I drag it,it will display the 2nd, 3rd and so on employees who meet those criterias.
Any advices?
Many thanks!
Best regards,
Ivan
Ivan, MATCH() always returns first found value. If you are on modern Excel with dynamic arrays, you may use something like
=FILTER($C$3:$C$100,($D3:$D100=$Q$4)*($G3:$G100=$Q$5)*($I3:$I100=$Q$6))Otherwise something like
=IFERROR(INDEX($C3:$C100,AGGREGATE(15,6,1/($D3:$D100=$Q$4)/($G3:$G100=$Q$5)/($I3:$I100=$Q$6)*(ROW($C3:$C100)-ROW($C$2)),ROW()-ROW($N$7))),"")and drag it down.
Both not tested.
3 Replies
Ivan, MATCH() always returns first found value. If you are on modern Excel with dynamic arrays, you may use something like
=FILTER($C$3:$C$100,($D3:$D100=$Q$4)*($G3:$G100=$Q$5)*($I3:$I100=$Q$6))Otherwise something like
=IFERROR(INDEX($C3:$C100,AGGREGATE(15,6,1/($D3:$D100=$Q$4)/($G3:$G100=$Q$5)/($I3:$I100=$Q$6)*(ROW($C3:$C100)-ROW($C$2)),ROW()-ROW($N$7))),"")and drag it down.
Both not tested.
- Thanks a lot!!! First one does the job perfectly!
Ivan, you are welcome
