Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 197
  • Last Modified:

lookup the first correct value within a date range.

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
jtencha
Asked:
jtencha
  • 3
  • 2
1 Solution
 
Ejgil HedegaardCommented:
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
 
gowflowCommented:
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
 
jtenchaAuthor Commented:
sorry, was away.  I'll play with these formulas and post back tomorrow how they worked. thanks
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
jtenchaAuthor Commented:
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
 
Ejgil HedegaardCommented:
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
 
jtenchaAuthor Commented:
Thanks!
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now