How can i use > in lookup function?

[Guest]

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

 

[Jason Morin]

One way:

=INDEX(B12:B407,MAX(IF(E12:E407>0.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

 

[Bala]

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