lookup first & last values within row & return column header value

[Matt]

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.

 

[Ron Coderre]

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:F2,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]

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

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:F2,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)

 

[Ron Coderre]

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)

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?

 

[Matt]

Thanks Ron

Works a treat.

--
Regards
Matt

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)

 

[T. Valko]

The rows beneath contain a "1" in each cell

when an employee will be on site.

For the last entry:

=IF(SUM(K11:BJ11),LOOKUP(2,K11:BJ11,K$7:B$J7),"N/A")

 

[Ron Coderre]

You're welcome, Matt....I'm glad I could help.


***********
Regards,
Ron

XL2003, WinXP

 

[Matt]

Sorry to bother you again Ron.

How would I change the formulas that you gave me (both for first value and
last value) so that it recognises any number rather than just a "1".

 

[T. Valko]

I am using the sheet to show manning levels

so that it recognises any number

I don't imagine you'd have negative manning? So, assuming the numbers are

=0:

For the first number (if any):

=IF(SUM(A2:F2),INDEX(A1:F1,MATCH(1,INDEX(--ISNUMBER(A2:F2),1,),0)),"N/A")

For the last number (if any):

=IF(SUM(A2:F2),LOOKUP(1E+100,A2:F2,A1:F1),"N/A")

Format both as DATE

 

[Matt]

Thanks Biff. Works great!
--
Regards
Matt

I don't imagine you'd have negative manning? So, assuming the numbers are

For the first number (if any):

=IF(SUM(A2:F2),INDEX(A1:F1,MATCH(1,INDEX(--ISNUMBER(A2:F2),1,),0)),"N/A")

For the last number (if any):

=IF(SUM(A2:F2),LOOKUP(1E+100,A2:F2,A1:F1),"N/A")

Format both as DATE

 

[T. Valko]

You're welcome. Thanks for the feedback!

 

[Jo]

Biff,

How can we modify this formula to recognise the last cell containing text
rather than the number 1?

 

[T. Valko]

Try this:

=IF(COUNTIF(K11:BJ11,"*"),LOOKUP(REPT("z",255),K11:BJ11,K$7:BJ$7),"N/A")

That will not *exclude* cells that contain formula blanks.