Return a value form a cell

[cronk]

I have a table with dates on the top of several columns with multiple rows of questions listed below on the most left column. If the question is answered yes, under the one of several date headings, I need a formula to return the date from that top row when the question is answered yes. The question could be answered yes more than once and I only want the most recent date.

 

[Jonmo1]

Are the dates sorted from oldest to newest / left to right?

 

[cronk]

Yes dates are oldest to newest from left to right in top row

 

[Jonmo1]

Great (things are always better when data is sorted...)

Try

=LOOKUP(2,1/(B2:J2="Yes"),B1:J1)

B1:J1 = your dates
B2:J2 = your answers

 

[markmzz]

Cronk,

Or try this array formula (Ctrl+Shift+Enter and not only Enter):

=LARGE(--(B2:P2="Yes")*($B$1:$P$1),1)

Markmzz

 

[cronk]

Works Perfect
Thanks!