I had a different interpretation, quite possibly incorrect, of the querry from that of Beamernsw. I take it that you want a list of students that have a value of 0 or 1 for either Math or English.
The values stored in the transitions sheet columns BT and EK would not lend themselves to evaluation of falling into a range between two numbers. I think that you may have meant BR for English and EI for Maths. Going on that assumption, this solution takes advantage of there not being any fractional values in those columns so a simple IF(OR... formula is used to identify the names of the students in a helper column (English & Math!AT):
=IF(OR(Transition!BR2=0,Transition!BR2=1,Transition!EI2=0,Transition!EI2=1),Transition!A2,"")
The names are then filtered, to remove blanks, into column A using the formula:
=IFERROR(INDEX($AT$1:$AT$210,AGGREGATE(15,6,(ROW($AT$1:$AT$210))/($AT$1:$AT$210<>""),ROWS(E$1:E1))),"")
The Maths values are then input with the formula:
=INDEX(Transition!DX$1:DX$207,MATCH($A6,Transition!$A$1:$A$207,0))
Similarly the English values with:
=INDEX(Transition!BG$1:BG$207,MATCH($A6,Transition!$A$1:$A$207,0))
Here is a copy of your file with the formulas applied:
Copy of Forum upload.xlsx
Let me know if you have any questions.
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks