Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
Solved

# lookup the first correct value within a date range.

Posted on 2014-04-28
Medium Priority
195 Views
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
Question by:jtencha
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 3
• 2

LVL 23

Accepted Solution

Ejgil Hedegaard earned 1200 total points
ID: 40028263
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

LVL 31

Expert Comment

ID: 40029206
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 Comment

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

Author Closing Comment

ID: 40035520
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

LVL 23

Expert Comment

ID: 40035817
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 Comment

ID: 40036132
Thanks!
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

: Microsoft Office Collaborate for free and online versions of Microsoft  Word, Excel, Powerpoint, OneNote, Onedrive , Email, Calendar etc. In short we can say that Microsoft office is a suite of servers, applications and services developed by  Micrâ€¦
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
Viewers will learn about various customizable options in Excel 2013.
Viewers will learn how to create a PivotTable and make basic changes to it in Excel 2013.
###### Suggested Courses
Course of the Month12 days, 6 hours left to enroll