Thanks to you all for your great help,

Harold


"Aladin Akyurek" wrote in message
...
Also...

=INDEX($B$1:$B$10,MATCH(C1,$A$1:$A$10,1)+(LOOKUP(C 1,$A$1:$A$10)<C1))

Harlan Grove wrote:
Bernie Deitrick wrote...

Assuming your table is sorted in ascending order based on
its first column, then you could use this:

=IF(NOT(ISERROR(MATCH(C1,A1:A10,FALSE))),VLOOKU P(C1,A1:B10,2),
VLOOKUP(INDEX(A1:A10,MATCH(VLOOKUP(C1,A1:B10,1) ,A1:A10)+1),
A1:B10,2))

...

You could, but it's awfully redundant. Looks like OP wants
approximate matching but in the reverse sense, i.e., match
the smallest value in the 1st column of the lookup table
that's equal to or greater than the value sought. If the OP
is looking for a simple VLOOKUP replacement, then sort the
lookup table by the 1st column in *DESCENDING* order and
use the formula

=INDEX(A1:B10,MATCH(C1,A1:A10,-1),2)

If the lookup table needs to be sorted by 1st column in
ascending order for display, it still doesn't require
such redundancy.

=INDEX(A1:B10,COUNTIF(A1:A10,"<"&C1)+1,2)

Next, since VLOOKUP returns a #N/A when the value sought is
less than the minimum value in the 1st column of the lookup
table, symmetry would imply that the OP's formula should
return #N/A when the value sought it greater than the
largest value in the 1st column of the lookup table. If such
functionality should be provided, the 1st formula above does
so. The second formula would need to be changed to

=IF(C1<=MAX(A1:A10),INDEX(A1:B10,COUNTIF(A1:A10,"< "&C1)+1,2),
#N/A)


--

[1] The SumProduct function should implicitly coerce the truth values to
their Excel numeric equivalents.
[2] The lookup functions should have an optional argument for the return
value, defaulting to #N/A in its absence.