Excel - VLOOKUP?

[dallaspg]

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.

 

[VBA Geek]

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]

Awesome!!! Thank you VBA Geek!!!!!

 

[Aladin Akyurek]

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

 

[dallaspg]

Yes, always ascending order.

 

[Aladin Akyurek]

Yes, always ascending order.

Ok. Thanks for the info.

 

[dallaspg]

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]

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]

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

 

[Aladin Akyurek]

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.