lookup formula

[mdot218]

Hello All

I am setting up a absence log at work, and am having some trouble with a lookup formula.
I have attached an example workbook. (my example is of only a few names, but the actual one will be approx 250 names)

On sheet 1 is the data entry. Names are listed down column C, and the dates are listed in Row 4. An "A" will be typed in the relevent cell when someone is absent.

On sheet 2, I want to do a summary of an individual. There is a drop down box to select a name. I am having trouble getting the date when an A is input on sheet 1.

When I select the name on sheet 2, how can I get the correct date from Row 4?

thanks

Darren

[Haseeb Avarakkan]

If you have duplicate names, this will only returns first employee's date

=INDEX(Sheet1!C4:H4,MATCH("A",INDEX(Sheet1!C5:H9,MATCH(C5,names,0),0),0))