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:

	A	B	C	D	E	F	G
1	Name1
2	Name2		A		Y		B
3	Name3
4	Name4	T					G
5	Name5
6	Name6			H
7	Name7	A				Y
8
9	Selection: Name2
10	Result Below
11	A
12	Y
13	B

<tbody>
</tbody>

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.