Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can i use > in lookup function? | Excel Discussion (Misc queries) | |||
Lookup Function - Specific Values | Excel Worksheet Functions | |||
Lookup Function Error | Excel Worksheet Functions | |||
Lookup Function Problem | Excel Discussion (Misc queries) | |||
Lookup function w/Text and Year | Excel Worksheet Functions |