VLOOKUP FUNCTION

[ashoulds]

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.

 

[Daniel.C]

An ugly one :
=IF(ISNA(VLOOKUP(C1,A1:B10,2,0)),INDEX(A1:B10,MATCH(C1,A1:A10,1)+1,2),VLOOKUP(C1,A1:B10,2,0))
Regards.
Daniel

 

[ashoulds]

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

 

[Mike Fogleman]

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