Help...possibly with LOOKUP

JuleeM13

New Member
I’m driving myself batty trying to figure out how to do this.

On Sheet2 I have column A and B.
- Every cell in column B has text (a kid’s name).
- Some cells in column A may have a letter (a grade) or may have nothing. Some may have the same letter. Like this…

Sheet2
ColA ColB
A Mary
B Susie
C Johnny
Debbie
C Mark

Sheet1 is were I will put my formulas. I want to be able to specify what letter (grade) I want to look for (in this case C) and then return all the names from Sheet2 column B that have grade C in column A. It could be just one name or it could be 10 names. So in this example on Sheet1 I would want to see this (each name in it’s own cell)…

Sheet1
Johnny
Mark

I can sort of get it to work with the lookup function but that will only give me the first result even if I put the formula in more than one cell. How on earth do I do this?

Then let’s take it a step further. Let’s say my situation is the same as above only I have a different sheet for each class that are all set up the same way. This time however I want to look up which kids have a grade C in all of the classes (worksheets).

Can someone please help me with each of these scenarios? Thanks!

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

stanleydgromjr

Banned
JuleeM13,

See my Private Message to you (top right hand corner of MrExcel, Welcome, JuleeM13., "Your Notifications:".

Have a great day,
Stan

MrExcel MVP
Sheet2, A2:B6

<TABLE style="WIDTH: 96pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=128 x:str><COLGROUP><COL style="WIDTH: 48pt" span=2 width=64><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 12.75pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 height=17 width=64> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 width=64> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl23 height=17>A

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22>Mary</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl23 height=17>B</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22>Susie</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl23 height=17>C</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22>Johnny</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 height=17> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl23>Debbie</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl23 height=17>C</TD>

<TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22>Mark</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 height=17> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22> </TD></TR></TBODY></TABLE>

Sheet1

<TABLE style="WIDTH: 42pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=56 x:str><COLGROUP><COL style="WIDTH: 42pt; mso-width-source: userset; mso-width-alt: 2048" width=56><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 42pt; HEIGHT: 12.75pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25 height=17 width=56>Grade</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" id=td_post_1882919 class=xl24 height=17>C</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl26 height=17>Count</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 height=17 align=right x:num>2</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl26 height=17>List</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 height=17>Johnny</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 height=17>Mark</TD></TR></TBODY></TABLE>

A2 a grade of interest.

A4:

=COUNTIF(Sheet2!A2:A6,A2)

A6:

Control+shift+enter, not just enter...
Code:
``````=IF(ROWS(\$A\$6:A6)<=\$A\$4,INDEX(Sheet2!\$B\$2:\$B\$6,
SMALL(IF(Sheet2!\$A\$2:\$A\$6=\$A\$2,
ROW(Sheet2!\$A\$2:\$A\$6)-ROW(Sheet2!\$A\$2)+1),ROWS(\$A\$6:A6))),"")``````
and copy down.

You could possibly create a series of lists, corresponding to the existing classes. Of course, if all the classes were in a single area the above scheme would immediately apply.

JuleeM13

New Member
Thank you. I don't exactly get it but it appears to be working! Is there a way to do exactly the same thing but instead of specifying "Sheet 2" to have it look for any worksheet other than sheet 1 where I'm doing my calculations? Each sheet would be set up the same. I guess like a sheet wild card? I will be adding other worksheets for different classes and it would be nice if it just searched for the students with the particular grade in any worksheet and list them without having to edit the formulas everytime I add a new worksheet/class. Thanks again.

ossuary

Active Member
I think a coded solution might be better than formulas if you will be using more than one sheet (this way, if you add or remove sheets, it will continue to work without changing anything).

Keep your first sheet named "Sheet1", and put a command button on it. In the command button's code, put this:

Code:
``````Private Sub CommandButton1_Click()
Dim s1 As Worksheet
Dim sht As Worksheet
Set s1 = Sheets("Sheet1")

If Sheets("Sheet1").Range("A2").Value = "" Then Exit Sub
myRow = 8

s1.Range("A8:B1001").ClearContents
s1.Range("A5").Value = 0

For Each sht In Sheets
If sht.Name <> "Sheet1" Then
myCount = sht.Range("B65536").End(xlUp).Row
If myCount > 0 Then
For cnt = 1 To myCount
If sht.Range("A" & cnt).Value = s1.Range("A2").Value Then
s1.Range("A" & myRow).Value = sht.Range("B" & cnt).Value
s1.Range("B" & myRow).Value = sht.Name
myRow = myRow + 1
s1.Range("A5").Value = s1.Range("A5").Value + 1
End If
Next cnt
End If
End If
Next sht
End Sub``````

You can name all of your other sheets based on whatever criteria you want to identify the class as (classroom number, teacher's name, whatever).

Cell A2 on Sheet1 is where you will put the grade you want information on.
Cell A5 will give you a count of how many matches were found.
Cell A8 and down will show the matching names, and Cell B8 and down will show the classroom that student was in (in case you have more than one student with the same name - easier to identify them this way!).

The code assumes that your other sheets won't have any header rows, just the names and grades. If you want header rows on the other sheets, change the part of the code that says:

Code:
``````        If myCount > 0 Then
For cnt = 1 To myCount``````

Code:
``````        If myCount > 1 Then
For cnt = 2 To myCount``````

Let me know if this helps.

stanleydgromjr

Banned
JuleeM13,

I have sent your workbook back. Please read the instructions on Sheet1.

Have a great day,
Stan

Replies
3
Views
196
Replies
12
Views
174
Replies
8
Views
148
Replies
1
Views
323
Replies
1
Views
154

1,191,228
Messages
5,985,399
Members
439,962
Latest member
max_york

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.
Allow Ads at MrExcel

Which adblocker are you using?

Follow these easy steps to disable AdBlock

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

Follow these easy steps to disable AdBlock Plus

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