Microsoft Excel - formula to create dynamic range based on 2 criterias

Hi,
I need a unique list based on criterias: subject and year group.
below is the screenshot of the spreadsheet  (i can not upload documents on the website: it doesn't work for me)
at the moment i have a formula in cell A43
=IF(ROWS(A$42:A42)<=COUNTIF($B$56:$B$206,$A$2),INDEX($A$56:$A$206,SMALL(IF($B$56:$B$206=$A$2,ROW($A$56:$A$206)-ROW(B56)+1),ROWS(A$42:A42))),"")

The subjects list is in cells  $B$56:$B$206
i want to return class from $a$56:$a$206
based on values in cell A2 and b2 (b2 value is part of the text in A:57:A206

sorry, I'm not able to attach a spreadhseet, document manager on the website comes blank (in Google chrome and IE) 

Thank you
Ewa

Try something like this, if I understand the question properly.
=INDEX(A56:A206,MATCH(B2,B56:B206,MATCH(A2,A56:A206,0)))

Thank you for your reply, however this formula would only return the first value that match the criteria, I need all of them returned (sorry, it's not clear on the picture, there is more than 1 class for the same subject)

Ewa,

For security reasons you have to zip your file before you try to upload it.  can you zip it and try again so we can help you better with your question.

Don

unique list based on 2 criteria.zip
Hi Donald, I added it now, I did had it in zip format, it was the window of the uploaded that was not coming up, it worked now thru.
Thank you for looking 

I have your file open and I have looked at it,  I dont see a year colum in your data.  If you put a filter on and select only maths you get

Class	Subject	Full Name
10x/Ma1	Maths	Teach 74
10x/Ma2	Maths	Teach 75
10x/Ma3	Maths	Teach 76
10x/Ma4	Maths	Teach 77
10x/Ma5	Maths	Teach 78
10y/Ma1	Maths	Teach 111
10y/Ma2	Maths	Teach 112
10y/Ma3	Maths	Teach 113
10y/Ma4	Maths	Teach 114
10y/Ma5	Maths	Teach 115
10y/Ma6	Maths	Teach 116

So I am not sure how the 11 plays into your cretiera.  you can use =countifs(range,criteria,range,critera.....) to make selections based on several conditions.  please define for me at least how you want to uniquely identify your data. 

Don

Hi, sorry for not being very clear. the year is either 7,8,9,10 or 11 and would be determined by the value in cell B2 (that would not be visible for the user but woudl change if the user changed a term in cell C2)  .

the year group is part of the class name so if the filter was on I.T. and the year was 11 then the classes displayed in A43:A54 should be: 11A/It1 if the year was 10 the class displayed woudl be 10A/It1
I managed to base it on 1 criteria: subject (A2) but i need to base it on another criteria which would be part of the name of the class that match the year.
hope it makes more sense now.
Thank you
Ewa
P.S.
(i tried to attach the spreadsheet (zip) again, got same problem: (no problem attaching pics)