David, We seem to have a disconnect here. What you're describing now is not
the way it was described in your original posting (OP).

Here's what my UDF does:
It takes the value passed to it in the first parameter as the lower limit to
look for, and the second value passed to it as the upper limit. Now, here's
the apparent disconnect: In your OP you said you wanted to find a match in
ROW 7 in a list that occupied cells A7 through AA7. So the UDF assumes that
the list to look through are in a single row that begins at column A (and it
looks at A7 [7 coming from the entered formula] to the right for the end of
the list.
That is what this line of code sets up:
Set searchList = Range("A" & searchRow & ":" & _
Range("A" & searchRow).End(xlToRight).Address)

Your modification of that to
Set searchList = Range("A7" & searchRow & ":" & _
Range("A18" & searchRow).End(xlToRight).Address)

is probably what has confused things. If searchRow contains 7, then your
formula actuall sets the range to A77 ("A7" with 7 appended to it) to A187
("A18" with 7 appended to it) and where ever that ends (probably out in
column IV, or IV187). So it's looking in the wrong places for the answers -
range A77:IV187 is probably what is being searched, and they probably are all
empty (zero).

It kind of looks to me now that your list to search through is in a column
from A7:A18 ?? Rather than in a row A7:AA7, or does each column with a value
to find something between also have it's own list in rows farther on down the
column? That is, could it be that a value in B1 has values to look through
in B7:B18?

I think the easiest thing to do now is for you to send me a sample of the
workbook attached to an email sent to (remove spaces):
Help From @ JLatham Site .com
Remind me of this discussion, and in the email or on the Excel sheet, make
notes about what you expect as results for the data you show on it.


"David" wrote:

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.