Column B has dates, 1 Jan 05 through 31 Jan 06. Column E has numbers .1,.2,
up through 6. I need a lookup function that will find the last value in
column E that is greater than .9 and then display the date in same row from
column B. This will be on a summary sheet and have a lookup function for each
of the sheets that follow. I had this but could not figure out how to use .9
and the make the formula on another sheet. "=LOOKUP(6,E12:E407,B12:B407)". I
want the 6 to be a .9 instead.
Help! Thanks Jim

One way:

=INDEX(B12:B407,MAX(IF(E12:E4070.9,ROW(E12:E407)-MIN(ROW
(E12:E407))+1)))

Array-entered, meaning press ctrl + shift + enter. To
learn more about array formulas, see:

http://www.cpearson.com/excel/array.htm

HTH
Jason
Atlanta, GA

-----Original Message-----
Column B has dates, 1 Jan 05 through 31 Jan 06. Column E
has numbers .1,.2,
up through 6. I need a lookup function that will find
the last value in
column E that is greater than .9 and then display the
date in same row from
column B. This will be on a summary sheet and have a
lookup function for each
of the sheets that follow. I had this but could not
figure out how to use .9
and the make the formula on another sheet. "=LOOKUP
(6,E12:E407,B12:B407)". I
want the 6 to be a .9 instead.
Help! Thanks Jim
.

Hello:
I have similar problem:
Dates are stored in the 2nd Row.
Headcounts are stored from row 3 onwards. 1 row per project.
I am able to LOOK UP dat from ROW_2 for the HC=1 in the lower row.
This is equivalent to the END DATE. Not sure how to find the START date
though.
A1="Start", B1="EndDt", C1=1-JAN-10, D1=C1+7, E1=D1+1 and so on
A2=Need a formula; B2=LOOKUP(1,C2:Z2, A$1:Z$1), C2=0,
D2=1,E2=2,F2=2,G2=1,H2=1, I2=0...Z2=0
Need a formula which will return the start date against the frist occurance
of NON ZERO number. In this case D2=1, hence the corresponding date from D1
should be returned,
B2=LOOKUP(1,C2:Z2, A$1:Z$1) is retruning the end date as long as the
headcount is 1. Need help to fetch this date if headcount were greater that 1.
Thanks...Bala




"AZHawkPilot" wrote:

Column B has dates, 1 Jan 05 through 31 Jan 06. Column E has numbers .1,.2,
up through 6. I need a lookup function that will find the last value in
column E that is greater than .9 and then display the date in same row from
column B. This will be on a summary sheet and have a lookup function for each
of the sheets that follow. I had this but could not figure out how to use .9
and the make the formula on another sheet. "=LOOKUP(6,E12:E407,B12:B407)". I
want the 6 to be a .9 instead.
Help! Thanks Jim

Bala wrote:
Hello:
I have similar problem:
Dates are stored in the 2nd Row.
Headcounts are stored from row 3 onwards. 1 row per project.
I am able to LOOK UP dat from ROW_2 for the HC=1 in the lower row.
This is equivalent to the END DATE. Not sure how to find the START date
though.
A1="Start", B1="EndDt", C1=1-JAN-10, D1=C1+7, E1=D1+1 and so on
A2=Need a formula; B2=LOOKUP(1,C2:Z2, A$1:Z$1), C2=0,
D2=1,E2=2,F2=2,G2=1,H2=1, I2=0...Z2=0
Need a formula which will return the start date against the frist occurance
of NON ZERO number. In this case D2=1, hence the corresponding date from D1
should be returned,
B2=LOOKUP(1,C2:Z2, A$1:Z$1) is retruning the end date as long as the
headcount is 1. Need help to fetch this date if headcount were greater that 1.
Thanks...Bala

For A2, the first date corresponding to a non-zero item:

A2 (array* formula)
=INDEX($C$1:$Z$1,1,MATCH(1,--($C$2:$Z$2<0),0))

Not sure what you're striving for in B2. I'm guessing you want the last
date equal to or exceeding A2 that corresponds to a contiguous block of
non-zero item?:

B2 (array* formula) **
=INDEX($C$1:$Z$1,1,MATCH(1,($C$1:$Z$1=A2)*($D$2:$ AA$2=0),0))

*Commit an array formula by pressing Ctrl+Shift+Enter

**Note we use a little trick here. We are actually checking whether the
/next/ cell has a zero, hence the range shift.