Excel index match array - showing all values in list-CodePudding

I have an excel table as follows, the yellow box is a drop down to select the year 7, 8 or 9

The students list in col G shows all the students in the selected year ( yellow cell E2) using the following array formula (from G3 which is copied down):

{=INDEX($A$3:$A$9,MATCH(0,IF($E$2=$B$3:$B$9,COUNTIF($G$1:G2,$A$3:$A$9),""),0))}

My question is: What can I replace the yellow E2 cell with to show all names in the list regardless of year? I've tried "*" and it doesn't work. Is there a way around this without altering the formula?

CodePudding user response：

Since I have commented, and it has been accepted by OP as well as

• Formula used in cell G3

=IFERROR(IF($E$2="",A3,INDEX($A$3:$A$9,MATCH(0,IF($E$2=$B$3:$B$9,COUNTIF($G$2:G2,$A$3:$A$9)),0))),"")

Please note array entered formula needs to be entered with CTRL SHIFT ENTER depending on Excel Versions.