lookup column header based on cell value found from row name

[mtwa1990]

I have had a search around and i cant find what im looking for as i dont know how to describe it well.

I have a holiday tracker for my staff i have their names listed down the first column and dates across the top. Each holiday is marked as H and Bank Holidays as BH.

What i would like is from that information a formula that i can put in a cell below a staff members name, the formula will find "H" in the corresponding row and show me the date, if i pull down that formula to the next few cells then each cell will find the next H and display the date etc

This will list all the dates a member of staff has booked off by just entering a H into the row hopefully the attached doc will help with the understanding of what i need.

Thank you for any help

[mehmetcik]

Try this formula in C14

=IFERROR(OFFSET($B$1,MATCH(C$13,$A:$A,0)-1,SMALL(IF(OFFSET($B$1,MATCH(C$13,$A:$A,0)-1,0,1,200)="","",COLUMN(OFFSET($B$1,MATCH(C$13,$A:$A,0)-1,0,1,200))),ROW()-13)-2) &" " &TEXT( OFFSET(B1,0,SMALL(IF(OFFSET($B$1,MATCH(C$13,$A:$A,0)-1,0,1,200)="","",COLUMN(OFFSET($B$1,MATCH(C$13,$A:$A,0)-1,0,1,200))),ROW()-13)-2,1,1),"dd/mm/yyyy"),"")

[Bo_Ry]

Please try at C14

Please Login or Register  to view this content.

[mtwa1990]

Thats brilliant, thank you for your help, didnt realise it was going to be so complex, no wonder i couldn't work it out