Vlookup a range of values

[OgnjenGlisic]

I am trying to create a function that takes present market index (Dow Jones for example) value, and tries and look at historical data to see when that valued occured again (when historically was Dow Jones at the level it is today). I truncated the data to get rid of decimal points, but still, the lookup does not always work. Is there a way to look up range of values (so if in between 650-750), without having to convert each data point to a range itself?

Much appreciated

 

[OgnjenGlisic]

Just thought about this some more:

Let's say Column A is my "Count", Column B are my Dow Jones values, and Column C is the date of those values, and in addition, lets say that the data is sorted in ascending order. Cell B4 is the current Dow level.

And let's say I want to figure out when was the Dow Jones at the level it reached today? So if Dow was 9,000 today, I want to go back and find what is the most recent date at which the Dow was at 9,000.

Formula I have is:
"=VLOOKUP(MATCH(B4,$B$20:$B$4861,1),$A$20:$C$4861,3,FALSE)"

The problem here is that this formula shows the highest value that is below the current level, but what I want it to do is to find the most recent date where the value was below the current level?

I apologize if this sounds confusing, and I would very much appreciate your help!


Og

 

[OgnjenGlisic]

Figured it out. If I am looking to find the earliest date when Dow Jones was at 850 from a range of daily values, the best thing to do is to set up an if statement to the right that says "If the value is less than 850, give me the date, otherwise give me blank (" "), and then use the Max(range) to find the "highest" date.

I guess the lesson is that the best way to isolate data in these situations is to use an if statement with "blank" as one of the options. Not sure if anyone cares, but it might be useful to some people in future.