Is there a way to return the next largest value using a vlookup function when
an exact match is not found? As of now, it keeps returning the value that
matches or the next smallest value.

An ugly one :
=IF(ISNA(VLOOKUP(C1,A1:B10,2,0)),INDEX(A1:B10,MATC H(C1,A1:A10,1)+1,2),VLOOKUP(C1,A1:B10,2,0))
Regards.
Daniel
"ashoulds" a écrit dans le message de
news: ...
Is there a way to return the next largest value using a vlookup function
when
an exact match is not found? As of now, it keeps returning the value that
matches or the next smallest value.

Thank you so much. You rock! Now I think I have it all figured out!

"Daniel.C" wrote:

An ugly one :
=IF(ISNA(VLOOKUP(C1,A1:B10,2,0)),INDEX(A1:B10,MATC H(C1,A1:A10,1)+1,2),VLOOKUP(C1,A1:B10,2,0))
Regards.
Daniel
"ashoulds" a écrit dans le message de
news: ...
Is there a way to return the next largest value using a vlookup function
when
an exact match is not found? As of now, it keeps returning the value that
matches or the next smallest value.

Can you use Match with a match type of -1? It will provide the position of
the next larger item as long as the table is sorted descending. Along the
lines of:
If IsError (Application.VLookUp(Mystrng, MyTable, MyCol, False)) Then
MyTable.Sort := xlDescending
MyPos = Application.Match(Mystrng, MyTable, -1)

MyPos would now equal the row within the table. From there you should be
able to get the cell address to reference from for your column offset.

Mike F

"ashoulds" wrote in message
...
Is there a way to return the next largest value using a vlookup function
when
an exact match is not found? As of now, it keeps returning the value that
matches or the next smallest value.