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
 

Some videos you may like

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

ThePencilQueen

Board Regular
Joined
Jun 26, 2002
Messages
109
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
 

IML

MrExcel MVP
Joined
Feb 15, 2002
Messages
1,743
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
 

Aladin Akyurek

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

goddi

New Member
Joined
Oct 8, 2002
Messages
4
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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,318
Messages
5,600,917
Members
414,416
Latest member
Nobu

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