Thread: Index/Match formula to find multiple data

I have a list of Stats with a variety of Managers and team members. I've included an example. What I want to do is to type in the month in cell A2 and select the manager in cell B2 and that manager's stats for that month will populate in cell A4:D9 depending on # of team members.

I have the following formula in cell A8 which will find the 1st team member.
=INDEX(Stats!$B$2:$F$9,MATCH(1,($A$2=Stats!$A$2:$A$9)*(Team!$B$2=Stats!$C$2:$C$9),0),MATCH(A$3,Stats !$B$1:$F$1,0))

How or what formula would I be able to use to find the next team member and so on.

Like I said this is a small sample to a very large database which will grow each month. Any help is greatly appreciated!



Thanks!

I assume you want to match the month up too?

If so, then in the STAT sheet, G2, enter: =IF(C2="","",SUMPRODUCT(--(A$2:A2=Team!$A$15),--(C$2:C2=Team!$B$15))) and copy down as far as you want...

then in H2 enter: =MAX(G:G)

In A17 of Team sheet enter:

=IF(ROWS($A$1:$A1)>Stats!$H$2,"",INDEX(Stats!B:B,MATCH(ROWS($A$1:$A1),Stats!$G:$G,0)))

copy down as far as you want...

in B17: =IF(ROWS($A$1:$A1)>Stats!$H$2,"",INDEX(Stats!D:D,MATCH(ROWS($A$1:$A1),Stats!$G:$G,0)))

copied to next 2 columns and down as far as you copied the other formula...