Thread: VLOOKUP to show multiple rows based on unique ID

Hello. I'm looking for a formula that would look up student ID numbers in one worksheet and find (and list) all rows of data for the same ID number found on another worksheet.

My example below shows how I have two worksheets - Currently Enrolled Students and Graduates. The first worksheet lists only Student ID's of those students who are currently enrolled. The next worksheet lists ID's and majors of our past graduates.

I need a formula that would look at the ID's in the first worksheet (Currently Enrolled) and list for me which of those students had previously graduated, based on data from the second worksheet (Graduates). Also, I need to know the major the students graduated with.

My problem is that with VLOOKUP I can find who graduated, and I can get a match for what major a person graduated with BUT I'm not able to get more than one row of match-ups for those who graduated with more than one degree.

Thanks for your help!

Currently Enrolled Students worksheet:
11111
33333
55555


Graduates worksheet:
11111 English
11111 Russian
22222 International Studies
33333 Communications
44444 Science
44444 Geology
55555 Art
55555 History
55555 Economics

What the Excel results should show assuming a correct formula has been entered:
11111 English
11111 Russian
33333 Communications
55555 Art
55555 History
55555 Economics

Assuming the Graduates worksheet has data listed in A2:B10, then in C2:

=A2&"_"&COUNTIF(A$2:A2,A2)

copied down... this counts each ID code.

Then in the Enrolled Sheet, assuming we can list the courses horizontally (simpler)... then if the codes are in A2:A5,

In B2 enter: =COUNTIF(Graduates!A:A,'Currently Enrolled'!A2) copied down to count matches in Graduates sheet

in C2 enter: =IF(COLUMNS($A$1:A$1)>$B2,"",INDEX(Graduates!$B:$B,MATCH($A2&"_"&COLUMNS($A$1:A$1),Graduates!$C:$C,0 )))

copied down and across as far as necessary.