# Creating a list by referencing a seperate table

#### Cennyan

##### New Member
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.

Last edited:

### Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

##### MrExcel MVP
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

##### New Member
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.

##### MrExcel MVP
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

##### New Member
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

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

##### MrExcel MVP
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

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

Correct.

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

 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

##### New Member
 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?

Replies
1
Views
257
Replies
5
Views
218
Replies
0
Views
693
Replies
0
Views
3K
Replies
4
Views
578

1,190,676
Messages
5,982,207
Members
439,768
Latest member
loukrs

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back