lookup the first correct value within a date range.

Posted on 2014-04-28
Medium Priority
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
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
  • Learn & ask questions
  • 3
  • 2
LVL 23

Accepted Solution

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

Value, column B
LVL 31

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
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.


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 23

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

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

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 the different options available in the Backstage view in Excel 2013.
Viewers will learn how to find and create templates in Excel 2013.

765 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