Excel - Formula to find first value meeting criteria in a specified range

Question

I have a unique date/time value in one worksheet (we will call it WS1).

Date & Time

01/01/2016 12:00:00

In another worksheet, I have two columns set up - we will call it WS2.

Date & Time

01/01/2016 11:00:00

01/01/2016 11:30:00

01/01/2016 12:00:00

01/01/2016 12:30:00

.....

Decimal Value

63.8

62.3

61.9

60.1

....

My goal is to use the value in WS1 to find the matching reference cell in WS2's date & time column. The referenced cell is then offset by 1 column to enter the decimal value column.

We now have the address to the corresponding value cell to the date & time in WS1. I now wish to specified range from this value cell to the bottom of the list of cells.

Once the range is specified, I wish to find the address of the first cell that matches a particular criteria in the range. For the purpose of this requirement, I will specify the logical evaluation to be Value < 60.5 . Once the first cell matching this criteria has its address identified, I wish to offset this address and find the corresponding date & time.

Steps summary:

1. Find address of matching date & time value from WS1 in WS2
2. Offset this address by 1 column to obtain the adjacent cell reference
3. Specify a range from this "adjacent cell reference" to the end of the values column
4. Find address of first value in values column that meets logic requirement
5. Offset this address by -1 to move back to date & time column
6. Obtain value from the address in step 5

Thanks for any help. I am trying to use INDIRECT, INDEX, MATCH, ADDRESS and OFFSET functions but I may have to just do it in VBA.

Answer 1

Try this:

=INDEX(Sheet4!A:A,MATCH(1,IF(Sheet4!A:A>=A1,IF(Sheet4!B:B<60.5,1,0),0),0))

It will find the first date time where column B is less than 60.5 that is after the date in A1.

It is an array formula. It needs to be confirmed with Ctrl-Shift-Enter.

Change the Sheet4 references to the sheet with your data.