# How to find last and first value meeting criteria in a series of values?

This is a sample of my input:

Then, I have midnights in a consequent days and I would like to achieve this result automatically:

To put it in words: I have datetimes in one column and I would like to find the last datetime which is before midnight of a day and first datetime which is after this midnight. There can be none, one or more than one datetime for one day. I also need to find a value which is in a column next to the original input.

FYI: input data are snapshots of my gasmeter. Number in Value column is a total cubic meters spent before time in first column. I need to find those border values to calculate spending in a day. I am able to do the calculation, but I cannot manage to find those values in the input.

Thank you!

-

You will want to use the `MATCH` function.  It is like `HLOOKUP` and `VLOOKUP` except it returns an index (1..n) rather than a value (and if you aren’t familiar with `HLOOKUP` and `VLOOKUP`, you should probably learn about them).  `MATCH(D2, \$A\$2:\$A\$14)` will return the index of the last value in `\$A\$2:\$A\$14` that is less than or equal to `D2`; i.e., (the index of) the last time before the midnight time in `D2`.  (This is in the form of an integer between 1 and 13, which may be a little confusing, but it’s all good.)  To avoid redundancy, it’s useful to use “helper columns”; so I put `=MATCH(D2, \$A\$2:\$A\$14)` into `I2`.  Then enter these formulae:

• `E2`  →  `=INDEX(\$A\$2:\$B\$14, \$I2, 1)`
• `F2`  →  `=INDEX(\$A\$2:\$B\$14, \$I2, 2)`
• `G2`  →  `=INDEX(\$A\$2:\$B\$14, \$I2+1, 1)`
• `H2`  →  `=INDEX(\$A\$2:\$B\$14, \$I2+1, 2)`

where we use `INDEX` to fetch the desired values from columns `A` and `B`.

I cleaned up your input a little, for testing/demonstration purposes:

and here are the results:

This will blow up for dates in Column `D` that are before `A2`.  To fix that, add another helper column (`J`) and change `G` and `H`:

• `J2`  →  `=IFERROR(I2, 1) + 1`
• `G2`  →  `=INDEX(\$A\$2:\$B\$14, \$J2, 1)`
• `H2`  →  `=INDEX(\$A\$2:\$B\$14, \$J2, 2)`
-
Thank you, this did the trick. Now that I see the solution, it doesn't look so complex. Thx – jakubka Mar 23 '13 at 23:35