Excel - VLOOKUP?

dallaspg

New Member
Joined
Sep 25, 2014
Messages
5
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.
 

Some videos you may like

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

VBA Geek

MrExcel MVP
Joined
Dec 16, 2013
Messages
2,857
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
Joined
Sep 25, 2014
Messages
5

ADVERTISEMENT

Yes, always ascending order.
 

dallaspg

New Member
Joined
Sep 25, 2014
Messages
5

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
Joined
Feb 14, 2002
Messages
85,201
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
Joined
Sep 25, 2014
Messages
5
Because I enter the depths manually and I know the last 2 to 6 depths will decrease significantly
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,519
Messages
5,596,635
Members
414,083
Latest member
Mrsash

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

Which adblocker are you using?

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
Top