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