# Find the "next largest" value after Vlookup?



## Mulderator (Feb 20, 1999)

I noticed an annoying limitation with Vlookup--you can't tell it to give you the next largest value in a range when it doesn't find a match. For example, if I am looking for 5 in a column of numbers 2,4,6,8, Vlookup will give you 4. It would be nice be able to select the next largest value. Anyone know how to do that?


----------



## Zack Barresse (Jul 25, 2004)

Hi Mulder,

Would this be the value between the Vlookukp result and the next value (i.e. median)? So if your list was 10,20,30,40 and the Vlookup was to return 20, you would want 25 or 21? Also, is your list in ascending order and/or have any duplicates? Can you post a small data set of examples along with the expected results?


----------



## Mulderator (Feb 20, 1999)

Just asking a general question.


----------



## Zack Barresse (Jul 25, 2004)

The answer is yes, then. 

The particulars are what is important in coming to a solution. You're better off using an Index/Match combination, if I understand your question correctly though. You can also use a UDF if you would like. There are a couple: VlookupNth() (by Peter Moran: http://www.vbaexpress.com/kb/getarticle.php?kb_id=8) and Mvlookup() (by Juan Pablo Gonzalez: http://www.mrexcel.com/board2/viewtopic.php?t=19517&highlight=next+value+vlookup). These may or may not suit what you're looking for though.

If you are only looking for a row/column offset, use the Index/Match combination. An example..

=INDEX(A1:B10,MATCH("string",A1:A10,0)+1,2)

It will search column A in rows 1 through 10, find an exact match of "string", then return the value in column B from the row underneath it.

There are also possible array formulas which you could use as well.

That help answer your question?

HTH


----------



## Mulderator (Feb 20, 1999)

firefytr said:


> The answer is yes, then.
> 
> The particulars are what is important in coming to a solution. You're better off using an Index/Match combination, if I understand your question correctly though. You can also use a UDF if you would like. There are a couple: VlookupNth() (by Peter Moran: http://www.vbaexpress.com/kb/getarticle.php?kb_id=8) and Mvlookup() (by Juan Pablo Gonzalez: http://www.mrexcel.com/board2/viewtopic.php?t=19517&highlight=next+value+vlookup). These may or may not suit what you're looking for though.
> 
> ...


I did know that you could do it with Index and Match, but that's a real awkward confusing way to do it--well beyond the capabilities of just about everyone except you and Mulder! 

I'll check out that function.

Thanks Zack.


----------



## Mulderator (Feb 20, 1999)

That function is nice, but if I'm not mistaken, it appears to be suited to when you want the next item where you have duplicates. What someone should do is code a function called "VLookupNH" or something similar for "Next Highest". So--for example, take the following data set.

x	A	B	C	D
1	-	-	-	-
2	1	-	-	-
3	2	-	-	4
4	5	6	7	6
5	7	8	8	7
6	8	9	9	8

If the Lookup Range is a1:d6 and I look up "4" in Column "A", I get "2", in Column "B" I get "0", in Column "C" I get "0", in Column "D", I get "4". Now, it would be nice if there were a function where if Lookup the Lookup value returned the next value higher than the lookup value because then you could have an If statement stating that If Vlookup gave you "0", then use "VLookupNH".

Now I know you can get around this by a contorted Index/Match function. Also, what I did was created another row of data using the "Min" function and did a VLookup for that so that if I got Zero, the formula chose the other Vlookup, which gave me the minimum value in the row. But that would be a nice function. I mean hell, it would be better if VLookup had another variable where you could choose to select the next lowest or next highest depending either by selection or have it default.


----------



## bomb #21 (Jul 1, 2005)

Mulderator said:


> I noticed an annoying limitation with Vlookup--you can't tell it to give you the next largest value in a range when it doesn't find a match. For example, if I am looking for 5 in a column of numbers 2,4,6,8, Vlookup will give you 4. It would be nice be able to select the next largest value. Anyone know how to do that?


*If* ordering the lookup range from high to low (8, 6, 4, 2) was an option:

*=INDEX(A1:A4,MATCH(x,A1:A4,-1))*

would return 6 for x = 5 or x = 6.


----------



## Mulderator (Feb 20, 1999)

bomb #21 said:


> *If* ordering the lookup range from high to low (8, 6, 4, 2) was an option:
> 
> *=INDEX(A1:A4,MATCH(x,A1:A4,-1))*
> 
> would return 6 for x = 5 or x = 6.


Yes, I know Index/Match will do it, but its cumbersome. You have to really focus on those formulas and its very easy to make mistakes, especially when you are pointing to ranges and cells in other shsets--you can drive yourself crazy. That's why I am saying a nice function to do it would eliminate all of that. Or change VLookup as follows:

VLookup(lookup_value,lookup_range,column,type, True/False)

Where "type" if True or omitted could be:

"1" - find a matching value or the lowest value less than the lookup value if no match
"0" - find a matching value or if no match, the lowest value greater than the lookup value or if there are no lesser values, the lowest value in the range (don't count blank cells as "0"). 
"-1" - find a matching value and if no match, the next highest value more than the lookup value and if there are no greater values, then the highest value in range.

If False, of course, you'd need an exact match only.

I think Anne Troy and her programmers should get on that!!!


----------



## bomb #21 (Jul 1, 2005)

Mulderator said:


> Yes, I know Index/Match will do it, but its cumbersome. You have to really focus on those formulas and *its very easy to make mistakes* ...


I can't believe *you* said that!


----------



## Zack Barresse (Jul 25, 2004)

VLOOKUP already has this ability - to a point. Use 1 or TRUE as the last syntactical value. From the Help files ...



> Range_lookup A logical value that specifies whether you want VLOOKUP to find an exact match or an approximate match:
> 
> If TRUE or omitted, an exact or approximate match is returned. If an exact match is not found, the next largest value that is less than lookup_value is returned.
> The values in the first column of table_array must be placed in ascending sort order; otherwise, VLOOKUP may not give the correct value. For more information, see Sort data.
> ...


To do anything else will take (generally) VBA.


----------

