I have what I think is a simple query but I cannot find anyone else asking the question so here goes.
I have a table showing the price of an item each day over a period of time. The table has a Date column (sorted ascending) and a Price column. The price goes up and down from day to day.
Given a specific date, I need to find the next date when the price dropped below that price.
So I need to find a row in the table and return the date column based on 2 criteria being Date > given date and Price < given price.
From what I can find VLookup will only find a table entry if it is a specific value.
I have also looked in Index Match but similarly I am not trying to Match a value but rather a condition.
Any assistance on what formula I could use would be greatly appreciated.
As an example if I was given 07Jan13 Price = 21, I need the formula to return 15Jan13 as this was the next date the Price was less than 21.
Date Price
31-Dec-12 20
02-Jan-13 24
03-Jan-13 19
04-Jan-13 22
07-Jan-13 21
08-Jan-13 22
11-Jan-13 23
12-Jan-13 21
15-Jan-13 20
16-Jan-13 18
18-Jan-13 17
Bookmarks