Many thanks for your assistance.
I seemed to have some issue in my 2003 excel.
Our sample was from A7 to A18 (Use your sampling -100 to 90)
So, your UDF would be:
Set searchList = Range("A7" & searchRow & ":" & _
Range("A18" & searchRow).End(xlToRight).Address)

Did I understand correctly?
How the function can be call?
It was seemed to me, that function returned only 0.
What did I do wrong?

I need all the answers, for instance in our sample, the result would be -10
in one sampling cell and 0 in other cell.

If we test B1 to C1 side, we would have answer 0, 10, 20, and 30.

Thanks.


"JLatham" wrote:

Now, having given that long solution above, you do realize that if you used a
simple
=LOOKUP(B1,$A7:$AA7,$A7:$AA7)
to find the value between A1 and B1 would return a result that may be useful
to you: the LAST number on row 7 that is <= B1
But I was assuming that you wanted the first number that met the "between"
criteria when I wrote up the UDF.

"David" wrote:

Hello,
I used conditional IF to write my formulas but when my table increase in
size, it became many IF statements in a cell.
I tried to replace IF with lookup function but some how I got so many ERR.
Can someone write me the formulas to solve my issue?

Here is the example:
It can be a row or column but let assume it is a row A1, B1, C1, etc.
Ie. A1 = -25, B1 = 0, and C1 = 31.

We have another row consist of A7 to AA7 for example.

Now we want to search through A7 to AA7 to find a value between A1 and B1
(from -25 to 0), B1 and C1 (from 0 to 31) etc...
If it does found, it will display the found value in a cell in question.

Thanks.