Trouble with Index and match formula

Hi All

I am working on an absence report. I previously got some great help which worked for a while, until I realised that I need to expand it, but not sure how to.

Sheet 1 is the data entry sheet, names in column B, and dates in row 4
Sheet 2 is a report page where you can select a name and see what dates they were absent on.

The formula in use on sheet 2 is:

=INDEX(Sheet1!$C$4:$H$4,MATCH("A",INDEX(Sheet1!$C$5:$H$9,MATCH($C$5,names,0),0),0))

What this does is return the first date of absence. I cant get it pick up the other dates.

Attached is an example

regards

Darren

In cell C8 try this little formula, and copy down as far as needed:

=IF(OR($C$5="",ISNA(MATCH($C$5,names,0))),"",IF(ROW(1:1)>COUNTIF(OFFSET(Sheet1!$C$5:$H$5,MATCH($C$5,names,0)-1,0),"A"),"",INDEX(Sheet1!$4:$4,LARGE(INDEX(COLUMN(Sheet1!C4:H4)*(OFFSET(Sheet1!$C$5:$H$5,MATCH($C$5,names,0)-1,0)="A"),0),COUNTIF(OFFSET(Sheet1!$C$5:$H$5,MATCH($C$5,names,0)-1,0),"A")-(ROW(1:1)-1)))))

thank you Andrew-R
You solved 2 problems in 1 hit. Very happy indeed.
Thanks again
Darren