return larger # w/vlookup

  • Thread starter Thread starter mike d
  • Start date Start date
M

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?
 
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.
 
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.
 
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))
 
Back
Top