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.