Date Look Up Formula

[gahooja]

I need help coming up with a formula which will pick up a date from a heading row if the rows below has a zero.

For example I have a spreadsheet with dates as the main heading and below i have names of resources with forecasted hours.

1/1 1/2 1/3 1/4 1/5 1/6 1/7
john doe 8 8 8 8 8 0 0
Jane doe 6 8 8 8 0 0 0
Mary 8 0 0 0 0 0 0
Mark 0 0 0 0 0 0 0

I need a formula which will pick up the date when i have zero showing as the hours for a certain individual. So for John i would need to show 1/6, Jane1/5, Mary 1/2, and Mark 1/1. I have this data for the whole year so don't want to search each individual manually but need a date when their hours go to zero basically.

Thanks

 

[tigeravatar]

gahooja,

Using your example and assuming the names are in column A rows 2 through 5 and the dates are in row 1 columns B through H:
=INDEX($B$1:$H$1,,MATCH(TRUE,INDEX(B2:H2=0,),0))

 

[gahooja]

Worked Perfectly. Thank You