# Vlookup a range of values

#### OgnjenGlisic

##### New Member
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

### Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

#### OgnjenGlisic

##### New Member

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

##### New Member
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.

Replies
4
Views
100
Replies
3
Views
287
Replies
5
Views
207
Replies
2
Views
93
Replies
6
Views
244

1,191,717
Messages
5,988,257
Members
440,145
Latest member
arxoon

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back