David, It is tough to do with a formula, I agree. LOOKUP() wants to return
the first value that is less than or equal to the lookup parameter, which can
cause erroneous results. To expand your example, assume on row 7 that the
following values exist beginning in column A (and I just took it out to K)
-100 -50 -26 -10 0 10 20 30 41 82 90
=LOOKUP(A1,A7:AA7,A7:AA7) returns -26 which is wrong! you needed -10 to be
returned (first number between -25 and 1)
Even trying to get fancier, using a formula like
=IF(AND(LOOKUP(A1,$A7:$AA7,$A7:$AA7)=A1,LOOKUP(A1 ,$A7:$AA7,$A7:$AA7)<=B1),LOOKUP(A1,$A7:$AA7,$A7:$A A7),LOOKUP(B1,$A7:$AA7,$A7:$AA7))
doesn't work properly, because while it returned 0 (which is between -25 and
0) it skipped -10 which would have been the first value that met the criteria.
So, the solution I have come up with is a User Defined Function (UDF), which
is simply VBA code that you can call from a formula in a cell just like any
built-in Excel function. The code for it is below and to put it to work, you:
open your workbook, press [Alt]+[F11] to open the
VB editor;
in the
VB Editor choose Insert -- Module and
copy the code below into the empty module presented to you and
close the
VB editor. Save the workbook.
Now, when you need to find a number between 2 numbers you use the function as:
=findfirstbetween(A1,B1,7)
to find the first value on row 7 that is between the values in A1 and B1, or
=findfirstbetween(B1,C1,7)
to find the first value on row 7 that is between the values in B1 and C1.
It's up to you to make sure that the numbers in the search list (row 7 in
our examples) are in ascending order from left to right. And when you enter
the cell address, the one with the smaller value should be entered first as
we have been doing all along.
Hope this helps some. Here is the code:
Function FindFirstBetween(lowLimitCell As Range, _
highLimitCell As Range, searchRow As Long) As Variant
'INPUT: lowLimitCell = address of cell with lower limit value in it
' highLimitCell = address of cell with upper limit value in it
' searchRow = row number with values to be searched
'OUTPUT: "No Match" if no values in searchRow are
' between low/high limits, OR
' the FIRST value in searchRow that is:
' greater than or equal to lowLimitCell value, and is
' less than or equal to highLimitCell value.
'Call format in an Excel cell example:
' =FindFirstBetween(A1,B1,7)
'would return first value from row 7 that is between the
'values in A1 and B1 on the same sheet with the formula.
'
Dim searchList As Range
Dim anySearchEntry As Range
'have to find out what cells to search in the search row
'assumes that entries begin in column A and
'continue without a break (empty cell) to the end of
'the list to search on that row
Set searchList = Range("A" & searchRow & ":" & _
Range("A" & searchRow).End(xlToRight).Address)
FindFirstBetween = "No Match"
For Each anySearchEntry In searchList
If anySearchEntry = lowLimitCell And _
anySearchEntry <= highLimitCell Then
FindFirstBetween = anySearchEntry
Exit For
End If
Next
Set searchList = Nothing ' housekeeping
End Function
"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.