# VLookup Default-change it?

#### goddi

##### New Member
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

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

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

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

Replies
8
Views
346
Replies
1
Views
183
Replies
2
Views
399
Replies
0
Views
229
Replies
3
Views
708

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?

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