Point well taken Harlan!

My test data wasn't sorted, but in retrospect, it should definitely have
been, since the OP's entire question revolved around *inexact* matches.

--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"Harlan Grove" wrote in message
...
"Ragdyer" wrote...
Another way - *non* array option:

With datalist in A1 to B20, and lookup value in C1,
try this:

=INDEX(B1:B20,MATCH(SMALL(A1:A20,COUNTIF(A1:A20," <"&C1)+1),A1:A20,0))
...

So close!

The MATCH and SMALL calls are superfluous when the first column of the
lookup range is sorted in ascending order. The general form

=INDEX(table,COUNTIF(INDEX(table,0,1),"<"&value_so ught)
+1,column_sought)

and the particular form matching your formula

=INDEX(B1:B20,COUNTIF(A1:A20,"<"&C1)+1)

produce the desired result. Further, these forms work when col A
contains text.