For the data below, I have no trouble getting the max of column F, the corresponding date for the max using INDEX, MATCH.. But what I am trying to lookup as well is the first date of that consecutive sequence. For the sample data below, I'm returning 3/13/2013 for the end date, but I need a way to lookup the first date (2/14/2013). Any ideas? I thought I could use offset to do that, but I'm not sure how to use it.
| Excel 2007 | ||||||||
|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | |||
| 2312 | 2/4/2013 | -0.68 | 305.45 | -0.68 | 38,093.00 | 1.00 | ||
| 2313 | 2/5/2013 | 0.03 | 305.48 | -0.65 | 32,631.00 | 2.00 | ||
| 2314 | 2/6/2013 | 0.32 | 305.80 | -0.33 | 43,171.00 | 3.00 | ||
| 2315 | 2/7/2013 | 0.01 | 305.80 | -0.32 | 44,576.00 | 4.00 | ||
| 2316 | 2/8/2013 | 0.07 | 305.87 | -0.25 | 37,833.00 | 5.00 | ||
| 2317 | 2/11/2013 | -0.01 | 305.87 | -0.26 | 34,531.00 | 6.00 | ||
| 2318 | 2/12/2013 | -0.18 | 305.68 | -0.44 | 38,298.00 | 7.00 | ||
| 2319 | 2/13/2013 | 0.85 | 306.54 | 0.00 | 60,774.00 | 0.00 | ||
| 2320 | 2/14/2013 | -0.34 | 306.20 | -0.34 | 43,835.00 | 1.00 | ||
| 2321 | 2/15/2013 | -0.19 | 306.01 | -0.53 | 42,279.00 | 2.00 | ||
| 2322 | 2/18/2013 | 0.04 | 306.05 | -0.49 | 23,403.00 | 3.00 | ||
| 2323 | 2/19/2013 | -0.24 | 305.81 | -0.73 | 33,089.00 | 4.00 | ||
| 2324 | 2/20/2013 | -0.45 | 305.36 | -1.18 | 42,210.00 | 5.00 | ||
| 2325 | 2/21/2013 | 0.51 | 305.87 | -0.67 | 59,661.00 | 6.00 | ||
| 2326 | 2/22/2013 | 0.03 | 305.90 | -0.64 | 29,427.00 | 7.00 | ||
| 2327 | 2/25/2013 | -1.65 | 304.25 | -2.29 | 48,188.00 | 8.00 | ||
| 2328 | 2/26/2013 | 1.22 | 305.47 | -1.07 | 52,669.00 | 9.00 | ||
| 2329 | 2/27/2013 | -0.10 | 305.36 | -1.17 | 41,346.00 | 10.00 | ||
| 2330 | 2/28/2013 | -0.06 | 305.31 | -1.23 | 37,180.00 | 11.00 | ||
| 2331 | 3/1/2013 | -0.23 | 305.08 | -1.46 | 50,600.00 | 12.00 | ||
| 2332 | 3/4/2013 | 0.57 | 305.65 | -0.89 | 40,864.00 | 13.00 | ||
| 2333 | 3/5/2013 | -0.08 | 305.58 | -0.96 | 36,688.00 | 14.00 | ||
| 2334 | 3/6/2013 | 0.18 | 305.75 | -0.79 | 43,602.00 | 15.00 | ||
| 2335 | 3/7/2013 | -0.37 | 305.38 | -1.15 | 39,742.00 | 16.00 | ||
| 2336 | 3/8/2013 | 0.16 | 305.54 | -0.99 | 39,138.00 | 17.00 | ||
| 2337 | 3/11/2013 | 0.01 | 305.55 | -0.99 | 29,766.00 | 18.00 | ||
| 2338 | 3/12/2013 | 0.27 | 305.82 | -0.71 | 45,529.00 | 19.00 | ||
| 2339 | 3/13/2013 | 0.34 | 306.17 | -0.37 | 38,655.00 | 20.00 | ||
Portfolio Data | ||||||||
