VLookup Default-change it?

goddi

New Member
Joined
Oct 8, 2002
Messages
4
The VLooup default is: if an exact match is not found, the next largest value that is LESS than Lookup_value is returned.
If my Lookup_value is 2011, I get a result of 16 (see below). But I want it to return the next largest value that is MORE than the Lookup_value, 17.
How can I do this? Thanks Gary
2006 16
2012 17
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
You *could* enlarge your lookup table to include an entry for each number

ie
2006 16
2007 17
2008 17
2009 17
2010 17
2011 17
2012 17

etc etc
 
Upvote 0
If your array is e1:F2 and the number of interest is in B2, you could try
=INDEX($E$1:$F$2,MATCH(B2,$E$1:$E$2,1)+(COUNTIF($E$1:$E$2,B2)=0),2)

Not fully tested. Good luck
 
Upvote 0
Let A1:B10 house the data of interest and C1 house the lookup value 2011.

=INDEX(B1:B10,MATCH(SMALL(A1:A10,COUNTIF(A1:A10,"<"&C1)+1),A1:A10,0))

I expect the formula IML posted will also work.

Addendum. A1:A10 must be in ascending order.
This message was edited by Aladin Akyurek on 2002-10-09 12:09
 
Upvote 0
Hi Aladin,
Thanks so much for the formula. It works! I am not sure yet exactly why, but I'll try to figure it out. Very helpful.
Gary (USA)
===========================
On 2002-10-09 12:08, Aladin Akyurek wrote:
Let A1:B10 house the data of interest and C1 house the lookup value 2011.
=INDEX(B1:B10,MATCH(SMALL(A1:A10,COUNTIF(A1:A10,"<"&C1)+1),A1:A10,0))
I expect the formula IML posted will also work.Addendum. A1:A10 must be in ascending order.
This message was edited by Aladin Akyurek on 2002-10-09 12:09
 
Upvote 0

Forum statistics

Threads
1,214,263
Messages
6,118,558
Members
448,836
Latest member
miclyzed

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
Back
Top