Help with Vlookup Function

[David Nolan]

Hey there.

I'm working with two sheets on a file. On the first sheet is a list of
students and the schools that they attend, with the student in column
A and the school in B. There are 200 rows. In sheet2, there is a list
of the schools and a countif for the number of students at each.

There are six students at School Alpha. I want to perform a lookup
that returns the names of each of those six students, as listed in
column A of sheet1. Any ideas?

I'd appreciate it if anyone had any advice. If you need clarification,
don't hesitate to ask. Thanks in advance.

 

[T. Valko]

Try this...

Using defined names in the formula:

Student refers to Sheet1$A$2:$A$200
School refers to Sheet1$B$2:$B$200

On Sheet2:

A2 = the lookup school name
B2 = formula: =COUNTIF(School,A2)

Enter this array formula** in C2:

=IF(ROWS(C$2:C2)>B$2,"",INDEX(Student,SMALL(IF(School=A$2,ROW(Student)),ROWS(C$2:C2))-MIN(ROW(Student))+1))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

Copy down to a number of cells that is at least equal to the max number of
students that attend any school.

 

[helene and gabor]

Hello Mr.Nolan,
On my Excel 2007 I set up your two tables. I found the following method:

Copy the table of: Students and Schools attended.
Sort this table by the column that contains school names. (Data,Sort, column
of school)
Delete all but the specified school part.

Regards,

Gabor Sebo

 

[David Nolan]

Thank you both for your assistance. Biff, I created the formula and
named ranges that you instructed, and made the formula an array
formula, but it comes up with a #NAME? error. When I enter it without
the brackets, it just gives a #VALUE error. Do you know why it would
be giving this error?

And Mr. Sebo, thank you for your tip, it is a workable solution that I
had not even considered. For the time being, I'm going to do something
like you said, but I'm going to keep trying to figure this formula
out.

Thanks again, guys.

~Dave

 

[T. Valko]

Here's a small sample file that demonstrates this.

Lookup with multiple results.xls 19kb

http://cjoint.com/?cBr2Y6eFX4

I put everything on one sheet to make it easier to see the results as
compared to the data.

--
Biff
Microsoft Excel MVP


Thank you both for your assistance. Biff, I created the formula and
named ranges that you instructed, and made the formula an array
formula, but it comes up with a #NAME? error. When I enter it without
the brackets, it just gives a #VALUE error. Do you know why it would
be giving this error?

And Mr. Sebo, thank you for your tip, it is a workable solution that I
had not even considered. For the time being, I'm going to do something
like you said, but I'm going to keep trying to figure this formula
out.

Thanks again, guys.

~Dave

 

[L. Howard Kittle]

Hi Biff,

Boy, that picture/sheet example was worth a thousand words...!

Had to add that to my archives and will spend some time studying the A-E
formula.

Regards,
Howard

 

[T. Valko]

Thanks! Hope you find it useful.

 

[helene and gabor]

Another idea:

copy your file of students vs. schools with headers on top,(say 200 rows)
somewhere below this table, say starting in the 210th row.
Click Data, Filter and click on the ditto mark put out by the filter on the
column for schools. Specify the school whose students you want to list. The
filtered list is your output, that can now be copied anywhere on the sheet.

Best Regards,

Gabor Sebo

PS. Thanks for your nice note!

 

[David Nolan]

Perfection. Biff, thank you so much for that file, it answered all of
my questions. Very sleek and sexy. Mr. Gabor, your solution worked as
well, but I prefer the ease of Biff's formula. Thank you both for your
help, I truly appreciate it.

~Dave

 

[T. Valko]

You're welcome. Thanks for the feedback!