VLookup Default-change it?

[goddi]

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

 

[ThePencilQueen]

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]

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]

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]

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