Dynamic Value Look Up Using a Flexible Date Range

Question

I am trying to extract the dates for the peak and first minimum date from streamflow data (hydrograph recession). Each year the timing of the peak changes; many years the minimum flow occurs more than once. The data includes 100 years of data (each year in a column) and daily values. Below is a brief sample of the data. In order to find the date that the minimum occurs, I need to use an indirect that only looks after the peak occurs (which is shown in Max Cell Address). The current approach (which does not work) is using an index( match( , indirect():,0),date)

The range is within the indirect is not coming out as desired. Any tips are appreciated.

22-Dec  158 183 139 307 337 337
23-Dec  151 150 146 369 337 250
24-Dec  151 150 182 433 337 250
25-Dec  222 151 182 433 337 222
26-Dec  158 151 182 369 369 250
27-Dec  184 150 201 307 369 222
28-Dec  146 170 206 278 337 369
29-Dec  150 165 170 222 307 337
30-Dec  154 175 177 171 278 337
31-Dec  158 191 256 146 278 278
Max Flow (cfs)  4470    1640    1360    6150    2220    3950
Max Flow Date   20-Jun  5-Jun   29-May  26-Jan  2-Jun   11-Feb
Max Flow Full Date  6/20/1911   6/5/1912    5/29/1913   1/26/1914   6/2/1915    2/11/1916
Max Cell Address    $B$174  $C$159  $D$152  $E$29   $F$156  $G$45
Row Number  174 159 152 29  156 45
Min Flow (cfs)  43  9   28  10  8   23

Min Flow Date

Is it as easy as switching =INDEX(MATCH(MinFlow,INDIRECT(MaxCellAddress:LastRow),0),DateColumn) for =INDEX(DateColumn,MATCH(MinFlow,INDIRECT(MaxCellAddress:LastRow),0))? Remember that the INDEX function wants the LookupRange before the Indexer value. — Stadem, May 16 '18 at 00:38
Maybe related: Find information about range of Excel data. Is it constantly growing or cyclical? — Scott - Слава Україні, May 16 '18 at 01:18
Stadem, that answer looks good to me. Would you submit it as an answer? — Sir Adelaide, May 16 '18 at 01:59

score 0 · Answer 1 · answered May 16 '18 at 15:41

In order to use an indirect (variable) range within the index(match()) formula, I set up the variable range in another cell then referenced the indirect to use the range in the other cell. The range was determined by using an index(match()) formula within the =cell('address', index(match())) formula. In the cell that lists the range it is set up to list the address of the first cell of the range, &":"& and then the =cell('address', last cell of desired range) (see below). This allowed me to drag the formula across all columns to create a flexible index match lookup.

=CELL("address",INDEX(B$4:B$276,MATCH(MAX(B$4:B$276),B$4:B$276,0)))&":"&CELL("address",$DF$368)

Dynamic Value Look Up Using a Flexible Date Range

1 Answers1