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.
 

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
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,210
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,210
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.
 
Master Excel Bundle

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.

Forum statistics

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

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