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

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

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,210
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
 
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,682
Messages
5,838,784
Members
430,568
Latest member
bortey

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