• Status: Solved
• Priority: Medium
• Security: Public
• Views: 199

# lookup the first correct value within a date range.

I need a lookup formula to find the price of a commodity that occurs within a date range.

Here is my example (see attached image)

Column A contains the date & time stamp, & column B is the price of commodity' Y' at that time.

Cell  D1 contains my customer's target price order for commodity 'Y'.
Cell D2 contains the time the target order was placed.

I want a vookup formula that will tell me if the customers target order (D1) was achieved
any time after he placed the order (D2) until the current time (i.e. =now()).

Note: the data in Column A will be sorted in accending order, & I just want to find the first instance of a positive result.

So in this example, the target was achieved at 4/20/14 7:00:00 pm (ROW 10)  when the commodity price in column B exceeded his target price of \$149.50.  The formula will return a result of \$150.00 (cell B10).  Note, in this example the formula will ignore the \$150.00 vaule in ROW2 becasue it occurred before he placed the target order.

thanks.
table.jpg
0
jtencha
• 3
• 2
1 Solution

Commented:
Time, column A
=INDEX(\$A\$2:\$A\$12,MATCH(1,INDEX((\$A\$2:\$A\$12>=\$D\$2)*(\$B\$2:\$B\$12>\$D\$1),,),0),1)

Value, column B
=INDEX(\$B\$2:\$B\$12,MATCH(1,INDEX((\$A\$2:\$A\$12>=\$D\$2)*(\$B\$2:\$B\$12>\$D\$1),,),0),1)
0

Commented:
I made a sample workbook where in Cell D3 you have this formula

and in Cell E3 you have this one

Basically D3 will lookup the first occurrence of the date/time put in D2 where the value is greater than the value put in E2

You can change the 100 in the formula to suits for your maximum data.
Rgds/gowflow
TimePrice.xlsx
0

Author Commented:
sorry, was away.  I'll play with these formulas and post back tomorrow how they worked. thanks
0

Author Commented:
hgholt, it worked like a charm.  By the way, what does the "(1" in this part of the formula, ...,MATCH(1,.... do?  I havent used this in an index/match before.

Thanks for the input also TimePrice.
0

Commented:
Index(Range,Row,Column) returns a single value.
When Row or Column is not used, an array of the whole range is returned.
So INDEX((\$A\$2:\$A\$12>=\$D\$2)*(\$B\$2:\$B\$12>\$D\$1),,) makes an array of 0 and 1.
0 when (\$A\$2:\$A\$12>=\$D\$2)*(\$B\$2:\$B\$12>\$D\$1) is false, and 1 when true.

Then MATCH(1,...) finds the position of the first true value (=1) in the array.

To see how it works:
In the formula edit line, highlight INDEX((\$A\$2:\$A\$12>=\$D\$2)*(\$B\$2:\$B\$12>\$D\$1),,) and press F9 to evaluate. Then Esc to get back.
0

Author Commented:
Thanks!
0

## Featured Post

• 3
• 2
Tackle projects and never again get stuck behind a technical roadblock.