Lookup function
 

Hi all,
One of my input workbook consist of 30 worksheets with the 2 colums
referring date and production profile. Below is an example
Column A ColumnB (production profile)
Date1 0
Date2 0
Date3 1
Date4 2
Date5 7
Date6 5
If production profile changes from 0 to <0, then date coresponding to that
production would be "start date".
Now would you help me to set up a formular for a cell to address the start
date of each case.
FYI, I have created a so-called "flag" where startdate has value 1 while
non-startdate has value 0. However, i do not know what step would be.
Tks so much for your help.
LA

Try:

=INDEX(A1:A6,1+MATCH(1,(B1:B6=0)*(B2:B7<0),0))

Array-entered, meaning press ctrl + shift + enter.

HTH
Jason
Atlanta, GA


"LA" wrote:

Hi all,
One of my input workbook consist of 30 worksheets with the 2 colums
referring date and production profile. Below is an example
Column A ColumnB (production profile)
Date1 0
Date2 0
Date3 1
Date4 2
Date5 7
Date6 5
If production profile changes from 0 to <0, then date coresponding to that
production would be "start date".
Now would you help me to set up a formular for a cell to address the start
date of each case.
FYI, I have created a so-called "flag" where startdate has value 1 while
non-startdate has value 0. However, i do not know what step would be.
Tks so much for your help.
LA

Great!!! Your idea really helps. Tks so much.
BTW, could you pls explain or provide me with any source explaining the
philosophy of the array calculation in this case. This formular just works if
I use "Ctrl+shift+enter" as suggested.

"Jason Morin" wrote:

Try:

=INDEX(A1:A6,1+MATCH(1,(B1:B6=0)*(B2:B7<0),0))

Array-entered, meaning press ctrl + shift + enter.

HTH
Jason
Atlanta, GA


"LA" wrote:

Hi all,
One of my input workbook consist of 30 worksheets with the 2 colums
referring date and production profile. Below is an example
Column A ColumnB (production profile)
Date1 0
Date2 0
Date3 1
Date4 2
Date5 7
Date6 5
If production profile changes from 0 to <0, then date coresponding to that
production would be "start date".
Now would you help me to set up a formular for a cell to address the start
date of each case.
FYI, I have created a so-called "flag" where startdate has value 1 while
non-startdate has value 0. However, i do not know what step would be.
Tks so much for your help.
LA