# Excel - VLOOKUP?

#### dallaspg

##### New Member
 9/1/14 25000 9/2/14 26000 9/3/14 27000 9/4/14 28000 9/5/14 29000

<tbody>
</tbody>
I have a column containing dates with corresponding depths in another column. I want to find the date when the depth exceeds a certain value. Example: If my depth s 27500, would expect the answer to be 9/3/14.

I have tried Vlookup and I have tried an array, but can't seem to get it to work.

Ideas most welcome.

### Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

#### VBA Geek

##### MrExcel MVP
are your depths always sorted ascending?

if so then

<b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">E6</th><td style="text-align:left">=LOOKUP(<font color="Blue">27500,B1:B5,A1:A5</font>)</td></tr></tbody></table></td></tr></table><br />

#### dallaspg

##### New Member
Awesome!!! Thank you VBA Geek!!!!!

#### Aladin Akyurek

##### MrExcel MVP
One question: Are those depth values always in ascending order?

#### dallaspg

##### New Member

ADVERTISEMENT

Yes, always ascending order.

#### dallaspg

##### New Member

ADVERTISEMENT

For the most part, this has worked very well. I've been using it in 4 different spreadsheets. Every once in a while, it will jump to the last date. If I make the criteria 27499.9, it will usually work, but I have a new case and the "fix" will not work. I also have a new case where all the depths out of 120 are ascending, but the last 6 descend.

Advise?
Thanks in advance!

#### Aladin Akyurek

##### MrExcel MVP
For the most part, this has worked very well. I've been using it in 4 different spreadsheets. Every once in a while, it will jump to the last date. If I make the criteria 27499.9, it will usually work, but I have a new case and the "fix" will not work. I also have a new case where all the depths out of 120 are ascending, but the last 6 descend.

Advise?
Thanks in advance!

LOOKUP, VLOOKUP with match-type set 1, as well as MATCH with match-type set to 1 all need an ascending match range. Can't you have all of then ascending (or descending)? If not, how do we know that just last 6 values are descending?

#### dallaspg

##### New Member
Because I enter the depths manually and I know the last 2 to 6 depths will decrease significantly

#### Aladin Akyurek

##### MrExcel MVP
Because I enter the depths manually and I know the last 2 to 6 depths will decrease significantly

If your data really requires LOOKUP (i.e., VLOOKUP with match-type set to 0 is inappropriate), a specification of 2 to 6 is a weak base to build upon.

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Threads
1,164,149
Messages
5,835,683
Members
430,375
Latest member
datdog22

### 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.
Allow Ads at MrExcel

### Disable AdBlock

Follow these easy steps to disable AdBlock

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

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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