Match value in Coulmn A and list all values in adjoining rows

[confused5]

I have a spreadsheet where I have a list of names in column A, and a bunch of dates listed in rows next to those names (upwards of dozens of dates spread across hundreds of columns eventually).

What I need to do, is look up the value in column A by selecting a name from a drop down, and then list all dates that are in the rows adjoining that name (some columns are blank then date, more blanks then another date [the column headers are course IDs]).

After losing two working days and the entire weekend trying to get this to work, I'm hoping someone finds this easy to solve.

As an example:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Name1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Name2[/TD]
[TD][/TD]
[TD]A[/TD]
[TD][/TD]
[TD]Y[/TD]
[TD][/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Name3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Name4[/TD]
[TD]T[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]G[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Name5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Name6[/TD]
[TD][/TD]
[TD][/TD]
[TD]H[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Name7[/TD]
[TD]A[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Y[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]Selection: Name2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]Result Below[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]A[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]Y[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]B[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I could manage to get it to display one value, and I could manage to get it to work if i was only ever looking on the same row, but once I tried to get it to work by looking for a name first and then base it on the name match on which row it should list all information in, it just stopped working. I searched low and high and whilst I could find variations of this question, it was never quite the same and I couldn't get it to work based off the other ones.

Thanks for reading.

 

[Andrew Poulsom]

Formula in A11 confirmed with Ctrl+Shift+Enter and copied to A12:A16:

=IFERROR(INDEX(INDEX(B$1:G$7,MATCH(A$9,A$1:A$7,FALSE),0),SMALL(IF(INDEX(B$1:G$7,MATCH(A$9,A$1:A$7,FALSE),0)<>"",COLUMN(B$1:G$7)-COLUMN(B$1)+1),ROWS(A$11:A11))),"")

 

[confused5]

Thanks Andrew, worked like a dream!

Just for my own education, would you mind shedding some light on what exactly COLUMN(B$1)+1) does in above? I think this is where I fell short in my own attempts and I don't really understand why this makes it work.

Thanks again, you're a life saver!

 

[Andrew Poulsom]

Well COLUMN(B1) returns 2 but we want the array to start at 1. So we deduct that 2 and add 1.