Solved

lookup the first correct value within a date range.

Posted on 2014-04-28
6
180 Views
Last Modified: 2014-05-01
I need a lookup formula to find the price of a commodity that occurs within a date range.
(preferrably a non-array{} formula please)

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
Comment
Question by:jtencha
  • 3
  • 2
6 Comments
 
LVL 21

Accepted Solution

by:
Ejgil Hedegaard earned 300 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 29

Expert Comment

by:gowflow
ID: 40029206
I made a sample workbook where in Cell D3 you have this formula
=IFERROR(INDEX($A$2:$A$100,MATCH(1,INDEX(($A$2:$A$100>=$D$2)*($B$2:$B$100>$E$2),,),0),1),"Not Found")

and in Cell E3 you have this one
=IFERROR(VLOOKUP(D3,$A$2:$B$100,2),"Not Found")

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

by:jtencha
ID: 40033272
sorry, was away.  I'll play with these formulas and post back tomorrow how they worked. thanks
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

Author Closing Comment

by:jtencha
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 21

Expert Comment

by:Ejgil Hedegaard
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

by:jtencha
ID: 40036132
Thanks!
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Excel 2010 very slow 13 241
Excel 2010 Merge of two tables 5 32
Excel 2010 - Select category per Max values v.2 2 47
Calculating Z-SCORE inside Excel. 4 84
Scenario: Your operations manager has discovered an anomaly in your security system. The business will start to suffer within 15 minutes if it is a major IT incident. What should she do? We have 6 recommendations for managing major incidents (https:…
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
Viewers will learn how to customize the ribbon and quick access toolbar in Excel 2013.
Viewers will learn a basic relationship technique in Power Pivot for Excel 2013.

919 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now