return larger # w/vlookup

[mike d]

is there a way to get the vlookup command to return the
larger of the two numbers that the lookup value falls
between?

 

[Akshay Bakhai]

A quick and dirty solution:

Add one more column to your table of entries. In this
column put the lookup values but shifted down by 1 row.

Thus, for example if column 1 of your lookup table reads
like:

A
D
F
H
R
X

then the new column should have following values
<blank cell>
A
D
F
H
R


And now use the same vlookup function but make it return
the results from this newly added column.

Hope this helps.

 

[Aladin Akyurek]

Hope this is what you're asking for...

Let LTable consist of

{2,"a";7,"d";9,"s";11,"v"}

As is clear, LTable is sorted in ascending order on its first column.

Let C3 house a lookup value such as 5.

=INDEX(LTable,MATCH(C3,INDEX(LTable,0,1))+(VLOOKUP(C3,INDEX(LTable,0,1),1)<>
C3),2)

would fetch "d" as result.

 

[Peo Sjoblom]

This formula by Harlan would be the equivalent of

=vlookup(A1,A2:B10,2

=INDEX(B2:B10,MATCH(SMALL(A2:A10,COUNTIF(A2:A10,"<"&A1)+1),A2:A10,0))