Creating a list by referencing a seperate table

[Cennyan]

I'm trying to create a list referencing a table such as below:

Name	Grade
Albert	A
Brian	B
Chris	C
Dave	D
Ethel	A
Frank	B
Gary	A
Henry	F

<tbody>
</tbody>

which will then list all students that have a particular grade. So referencing the above, the final result would look like:

Students with A's

Albert

Ethel

Gary

<tbody>
</tbody>

Using

=INDEX(A:A,MATCH("A",B:B,0))

I can get the first value of A to populate, the additional cells return the same value (IE, they all return Albert). I had thought to use COUNTIF

=INDEX(A:A,MATCH("A",COUNTIF($C$2:C2,B:B),0))

where $C$2:C2 Is the actual column which contains the formula. 2nd cell would be $C$2:C3 and so on. Using this formula it simply returns a 0.

 

[Aladin Akyurek]

Let A1:B9 house the data you posted, headers included.

Let C1 house A (a grade of interest).

In C2 enter:

=COUNTIFS($B$2:$B$9,C$1)

In C4 control+shift+enterm not just enter, and copy down:

=IF(ROWS(C$4:C4)>C$2,"",INDEX($A$2:$A$9,SMALL(IF($B$2:$B$9=C$1,ROW($A$2:$A$9)-ROW($A$2)+1),ROWS(C$4:C4))))

 

[Cennyan]

I found that the reason it was returning 0 is because I didn't have a value in the cell it was referencing and couldn't return it. The final formula I used which works perfectly is:

{=INDEX('Master Names'!F:G,SMALL(IF('Master Names'!G:G="A",ROW('Master Names'!G:G)),ROW(9:9)),1)}

If there is a more efficient or better way to do this let me know please.

 

[Aladin Akyurek]

I found that the reason it was returning 0 is because I didn't have a value in the cell it was referencing and couldn't return it. The final formula I used which works perfectly is:

{=INDEX('Master Names'!F:G,SMALL(IF('Master Names'!G:G="A",ROW('Master Names'!G:G)),ROW(9:9)),1)}

If there is a more efficient or better way to do this let me know please.

Perfectly? See post #2.

 

[Cennyan]

One other addendum. I want to add a column for absences, let's say it's D. I want to query D2:D9 and if there is an X in the column indicating that the individual had an absence, skip the final result regardless of the A grade so that the name isn't listed. How would I work that into Aladin's or my formula?

 

[AliGW]

How can a student achieve an A grade if they are absent? Surely the grade column will be empty?

 

[Aladin Akyurek]

One other addendum. I want to add a column for absences, let's say it's D. I want to query D2:D9 and if there is an X in the column indicating that the individual had an absence, skip the final result regardless of the A grade so that the name isn't listed. How would I work that into Aladin's or my formula?

So, if D = x, do not include that sudent in the result list, right?

 

[Cennyan]

So, if D = x, do not include that sudent in the result list, right?

Correct.

 

[Aladin Akyurek]

So, if D = x, do not include that sudent in the result list, right?

Correct.

Row\Col	A​	B​	C​	D​	E​	F​	G​
1​	Name	Grade		Absence			A
2​	Albert	A		X			2​
3​	Brian	B					name
4​	Chris	C		X			Ethel
5​	Dave	D					Gary
6​	Ethel	A
7​	Frank	B
8​	Gary	A
9​	Henry	F		X

In G2 just enter:

=COUNTIFS($B$2:$B$9,G$1,$D$2:$D$9,"<>X")

In G4 control+shift+enter, not just enter, and copy down:

=IF(ROWS(G$4:G4)>G$2,"",INDEX($A$2:$A$9,SMALL(IF($B$2:$B$9=G$1,IF(1-($D$2:$D$9="X"),ROW($A$2:$A$9)-ROW($A$2)+1)),ROWS(G$4:G4))))

 

[Cennyan]

Row\Col	A​	B​	C​	D​	E​	F​	G​
1​	Name	Grade		Absence			A
2​	Albert	A		X			2​
3​	Brian	B					name
4​	Chris	C		X			Ethel
5​	Dave	D					Gary
6​	Ethel	A
7​	Frank	B
8​	Gary	A
9​	Henry	F		X

<tbody>
</tbody>

In G2 just enter:

=COUNTIFS($B$2:$B$9,G$1,$D$2:$D$9,"<>X")

In G4 control+shift+enter, not just enter, and copy down:

=IF(ROWS(G$4:G4)>G$2,"",INDEX($A$2:$A$9,SMALL(IF($B$2:$B$9=G$1,IF(1-($D$2:$D$9="X"),ROW($A$2:$A$9)-ROW($A$2)+1)),ROWS(G$4:G4))))

That's fantastic and after some thinking I understand it. So from here, if I want to do multiple columns which are not next to each other (ie..instead of D2:D9 I want to do D:D, F:F, H:H, etc...) i'm using this for the COUNTIFS:

=COUNTIFS('Master Names'!$G:$G,Sunday!$Q16,'Master Names'!$J:$J,"<>X",'Master Names'!$P:$P,"<>X",'Master Names'!$V:$V,"<>X",'Master Names'!$AB:$AB,"<>X",'Master Names'!$AH:$AH,"<>X")

And this for the "G4" cell:

=IFERROR(INDEX('Master Names'!$F:$F,SMALL(IF('Master Names'!$G:$G=$Q$16,IF(1-('Master Names'!$J:$J="X"),ROW('Master Names'!$F:$F)-MIN(ROW('Master Names'!$F:$F))+1)),ROWS(K$13:K13))),"")

I believe I had to add the MIN function as I was using F:F instead of a specific cell. How can I reference the same columns listed in the COUNTIFS in the IF(1-('Master Names'!$J:$J="X"). The INDEX above works perfectly in weeding out anyone with an X in J:J and it creates a consecutive list. I don't believe I can use OR / AND in an array so not sure but would it be possible to clean up the COUNTIFS using an internal array of some type for the different columns referencing X and then use the same type of array for the INDEX statement?