Solved

lookup the first correct value within a date range.

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

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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Excel - Sum Formula 3 80
Excel Sheet Comparison Macros 16 64
Google  (Get  Users Email) 2 136
split user ID and domain name from email addresses using formula 3 18
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
: 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…
Viewers will learn various types of data validation for different data types in Excel 2013.
Video by: Zack
Viewers will learn about using Excel in a browser with Excel Online.

860 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