Display all values that meet certain criteria only - not pivot table

[gwstudent]

Hi -

I have posted a sample file here: https://docs.google.com/open?id=0B2V4Nkca2ASFSXlQWWdFWm0zV2c


I have a table in the second worksheet with misc values. In the first worksheet I would like to list all the values from the second that meet certain criteria - like a cross tab query. For example, show values that are for School A and Spring. I don't want to use a pivot table and prefer some formula that I can copy down and it shows nothing once the criteria are no longer met. I have used the below formula that works (sort of) however it repeats the same value. I just get the first record only and the values are repeated in subsequent cells. Am I using the wrong approach?


=INDEX(Course,MATCH("Spring"&"SchoolA",Term&School),0)


NOTE: I notice the link works in IE but FF and Chrome send to google login page.

 

[Faseeh]

Hi gwstudent,


In your sheet Roadmap, enter in cell C3 this formula, drag to F10 (to right and downward):

=IFERROR(INDEX(Course,SMALL(IF((Term=C$2)*(School="School"&$B$3),ROW(Term)),ROW(A1)),0),"")


In the same sheet in Cell C11 enter :


=SUM(((C3:C10)<>"")*1)

For both formula press Ctrl+Shift+Enter.


Regards,

Faseeh