Thanks Ron

Works a treat.

--
Regards
Matt


"Ron Coderre" wrote:

Here you go....

Your example began in Col_A, so no offset adjustment was necessary.

With the new information,
try this:
=IF(COUNT(K11:BJ11),INDEX($K$7:$BJ$7,
LOOKUP(10^10,K11:BJ11,COLUMN(K11:BJ11))-COLUMN(J11)),"n/a")

or this (with a hardcoded offset of 10)
=IF(COUNT(K11:BJ11),INDEX($K$7:$BJ$7,
LOOKUP(10^10,K11:BJ11,COLUMN(K11:BJ11))-10),"n/a")

Does that help?
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

"Matt" wrote in message
...
Thanks Ron

The formula for the first value works fine however the formula for the
last
value returns a #REF!.

The specific formula that I am using is as follows (different range of
data
obviously)

=IF(COUNT(K11:BJ11),INDEX($K$7:$BJ$7,LOOKUP(10^10, K11:BJ11,COLUMN(K11:BJ11))),"n/a")

What do you think?

--
Regards
Matt


"Ron Coderre" wrote:

With your posted data in A1:F4

This formula returns the data corresponding to the first 1 in Row_2:
G2: =IF(COUNT(A2:F2),INDEX($A$1:$F$1,MATCH(1,A2:F2,0)) ,"n/a")

....and this one returns the data corresponding to the last 1 in Row_2:
H2:
=IF(COUNT(A2:F2),INDEX($A$1:$F$1,LOOKUP(10^10,A2:F 2,COLUMN(A2:F2))),"n/a")

Format those cells as Dates.
Copy those formulas down as far as you need.

In the above example,
G2 returns 01/07/2008
H2 returns 02/04/2008

Is that something you can work with?
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

"Matt" wrote in message
...
I have a spreadsheet with data as follows:

A B C D
E
F
1 01/07/08 01/14/08 01/21/08 01/28/08 02/04/08 02/11/08
2 1 1 1 1
1
3 1 1 1
1
1
4 1 1
1
1

I am using the sheet to show manning levels for each week of a project.
Row
1 contains the dates of each week. The rows beneath contain a "1" in
each
cell when an employee will be on site.

I am trying to lookup the first value in each row and return the
relevant
date from row 1 above (eg on row 3 the required result would be
01/14/08).
Similarly, I would also like to lookup the last value in each row and
return
the date from row 1 above (eg on row 4 the required result would be
02/11/08).

Any help with this would be greatly appreciated. Thank you.
--
Regards
Matt