Vlookup formula not working

**rbwen** · 12-02-2011, 02:05 PM

Noob...be gentle

I need help with a formula. I've used vlookup in the past but I don't think it will get the job done on this one.

Situation: I have two sheets in my workbook. One Sheet1 I have a column (column A) with 100 student's names in it. I also have a column (column B) with Teacher's names in it.

On Sheet2 (an individual teacher's page) I want to be able to write a formula that will look on the top of the sheet (in cell A1) to find the teacher's name, then reference every instance of that name on Sheet1 (column B) and return all of the student's names from column A).

If someone has a formula or an example spreadsheet to pass along I would greatly appreciate it.

Ray

**darkyam** · 12-02-2011, 02:57 PM

Welcome to the forum. There's not a really simple formula to do what you want to do, but you can see if this works: put

Please Login or Register  to view this content.

in B1 and drag down as needed. In C1, put

Please Login or Register  to view this content.

and drag down and over as needed.

**rbwen** · 12-02-2011, 11:29 PM

Thanks for the formulas. It seems to work well for the first two returns and then it gets weird. I've attached a sample file so you can see what it's doing. The D column on Sheet2 is what's strange.

Also, how nicely do these formulas play with Google Spreadsheets?

**darkyam** · 12-02-2011, 11:41 PM

I made two minor errors. I didn't put in a ,0 in one of the match statements (telling it to look up an exact match), and I didn't freeze one of the references to a column.

I also think we had a different layout in mind. This is more what I had been envisioning, just one line for each teacher.

**rbwen** · 12-03-2011, 12:02 AM

That looks like it will do it. Thanks!

I appreciate your time and effort. Have a great weekend...rb

**rbwen** · 12-08-2011, 07:31 PM

one further step. the results I'm getting back have a bunch of empty cells between them. example, A1 returns a name and then the next name doesn't show up until A17. how do i get the results to bunch up?

**darkyam** · 12-08-2011, 08:07 PM

The easiest way is to highlight the list of teachers in the first sheet, then go to Advanced Filter, check Unique Records Only, then Copy to Another Location. Choose the range in Sheet 2 for the location.

**Haseeb Avarakkan** · 12-08-2011, 08:12 PM

Can't see the attachment here.

Sheet2!A1 = Teacher Name

In Sheet2!B1 enter, =COUNTIF(Sheet1!B:B,A1)

Then A3 enter this ARRAY FORMULA (must hit CTRL+SHIFT+ENTER, rather than just ENTER) then copy down.

=IF(ROWS(A$3:A3)<=$B$1,INDEX(Sheet1!$A:$A,SMALL(IF(Sheet1!$B$1:$B$1000=$A$1,ROW(Sheet1!$B$1:$B$1000)),ROWS(A$3:A3))),"")

Will give the all students name where teacher = A1