lookup the first correct value within a date range.

Posted on 2014-04-28
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.

Question by:jtencha
  • 3
  • 2
LVL 21

Accepted Solution

Ejgil Hedegaard earned 300 total points
ID: 40028263
Time, column A

Value, column B
LVL 29

Expert Comment

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.

Author Comment

ID: 40033272
sorry, was away.  I'll play with these formulas and post back tomorrow how they worked. thanks
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.


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.
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.

Author Comment

ID: 40036132

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Marketing can be an uncomfortable undertaking, especially if your material is technology based. Luckily, we’ve compiled some simple and (relatively) painless tips to put an end to your trepidation and start your path to success.
HOW TO: Install and Configure VMware vSphere Hypervisor 6.5 (ESXi 6.5), Step by Step Tutorial with screenshots. From Download, Checking Media, to Completed Installation.
Viewers will learn the basics of printing in Excel 2013 and how to adjust some common settings.
Viewers will learn the basics of the new Quick Analysis feature in Excel 2013.

708 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

15 Experts available now in Live!

Get 1:1 Help Now