(Note: I authored this question using Courier New font. It ended up in Ariel font. Please to a copy/paste function to a word preocessor and change it back to Courier New to see the correct layout of the sample table. Sorry!)
I want to automate a vertical table in Excel to find multiple occurrences of an item and list them horizontally. The original list (Col A, B, C in the example below) will be updated with a few changes on a monthly basis. VLOOKUP only finds the first of multiple entries in the table.
Example: I want to search a sequential list in ColB using a value from ColA and list the results in ColD, E, F, etc. Specifically, for those designated as "Tchr" (an entry in ColA) I want to find all "Studnt's" for that Tchr's ID in ColB and list them on the same line as the Tchr. The Student IDs are in ascending sequence.
In D2, I can specify:
=IF(A$2>"",VLOOKUP(A$2,$B$
2:$C$9,2,F
ALSE),"")
That gives me Betty as the first student in D2 for Jim. But I can't figure a way to get Pete as the next student in cell E2. I need a "VLOOKUP NEXT" function; that is, a way to index through the table (B2:C9) starting after the first hit of Betty until I run out of HP03 values (#N/A value on the VLOOKUP) for Jim as a Tchr.
A B C D E F G
1 Tchr Studnt Name ---Students of Tchr---
2 HP03 HP01 Jim Betty Pete Nick
3 HP01 Mary
4 HP01 Bill
5 HP01 HP02 Mike Jim Mary Bill
6 HP02 Sue
7 HP03 Betty
8 HP02 HP03 Pete Mike Sue
9 HP03 Nick
After locating Betty, I can use MATCH to find her location on the list but I can't get the resulting location to be plugged into a VLOOKUP function in E2 to start searching at the name after Betty.
Thanks for your assistance,
JoeBill
Start Free Trial
