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

M

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.
 
R

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)
 
M

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


Ron Coderre said:
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)
 
R

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)

Matt said:
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?
 
M

Matt

Thanks Ron

Works a treat.

--
Regards
Matt


Ron Coderre said:
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)
 
R

Ron Coderre

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


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

XL2003, WinXP
 
M

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

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

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
 
M

Matt

Thanks Biff. Works great!
--
Regards
Matt


T. Valko said:
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
 
J

Jo

Biff,

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

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top