Help with array formula

[alombia]

A	B	C	D	E	F	G	H	I…
1	Name	Year	Eng 1	Eng 2	Eng 3	Mat1	Mat2	Mat 3…
2	John	1	34	23	57	97	90	34
3	Sam	1	34	76	87	34	10	90
4	Luke	2	67	98	45	90	34	98
5	Lucy	3	98	67	45	67	86	90
6	May	3	7	54	76	23	94	38

<tbody>
</tbody>

Hi,
I would like help with a formula to retrieve filtered results from the above Sheet called Sheet1 and pasted into Sheet2 in say Cell A1
There are two selection criteria: Year and Subject. I need the formula to display all the results matching the selected criteria. For example, Year 1 and Eng. So Cell A1 has the Year value. Cell A2 has the subject value (subjects are in cell D1:I1)
I’m guessing an array formula would be good so that it can be pasted over into different cells.

 

[James006]

...Sorry ... since in cell B2 you do have your list of Unique individuals ...

you should test the formula in cell C2 ... :wink:

 

[63falcondude]

This works fine for the most part, but where there are duplicate names in sheet 1 column A, it produces incorrect data. For example:

In the above, you can see that A3 and A6 have same names but different Years in B. But your formula retrieves A3, even when '3' is selected in A2 of sheet2. What is happening?

I see. Try the corrected formulas.

Excel 2010
	A	B	C	D	E
1	Year	Subject
2	3	Eng
3
4	Name	Year	Eng 1	Eng 2	Eng 3
5	Lucy	3	98	67	45
6	May	3	7	54	76
Sheet2

Cell Formulas
Range	Formula
C5	=IFERROR(1/(1/SUMPRODUCT(($A5=Sheet1!$A$2:$A$6)*($B5=Sheet1!$B$2:$B$6)*(C$4=Sheet1!$C$1:$H$1)*(Sheet1!$C$2:$H$6))),"")
C4	{=IFERROR(INDEX(Sheet1!$C1:$H1,SMALL(IF(LEFT(Sheet1!$C1:$H1,3)=$B$2,COLUMN(Sheet1!$C1:$H1)),COLUMN(A$1:A$6))-2),"")}
A5	{=IFERROR(INDEX(Sheet1!$A$2:$A$6,SMALL(IF(Sheet1!$B$2:$B$6=$A$2,ROW(Sheet1!$B$2:$B$6)-1),ROW(Sheet1!1:1))),"")}
B5	{=IFERROR(INDEX(Sheet1!$B$2:$B$6,MATCH(A5&$A$2,Sheet1!$A$2:$A$6&Sheet1!$B$2:$B$6,0)),"")}
Press CTRL+SHIFT+ENTER to enter array formulas.