Substitute IF statement.

[David]

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.

 

[JLatham]

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 lik
=IF(AND(LOOKUP(A1,$A7:$AA7,$A7:$AA7)>=A1,LOOKUP(A1,$A7:$AA7,$A7:$AA7)<=B1),LOOKUP(A1,$A7:$AA7,$A7:$AA7),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

 

[JLatham]

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]

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.

 

[David]

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

 

[David]

Set searchList = Range("A7" & searchRow & ":" & _
Range("A18" & searchRow).End(xlToRight).Address)

Sorry, I failed to check (x1 to the right).
Fortunately, last night, I did also test your original version as well and I
got the same result.

 

[David]

Set searchList = Range("A7" & searchRow & ":" & _
Range("A18" & searchRow).End(xlToRight).Address)

Sorry, I failed to check (x1 to the right).
Fortunately, last night, I did also test your original version as well and I
got the same result.

 

[JLatham]

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]

JLatham:
Thanks for helping me out.
I realied I made a mistake in mod your UDF.
I also found the issue I have was due to excel itself.
I found in some instances, excel did logical comparison wrong.
In one instance, the output from lookup function was 20 and the other one
was 450.
In my IF statement, I wrote if cell with 20 was greater than cell with 450.
I got the wrong answer. I plugged in number instead and it worked just fine.
I think MS needs to redefine the lookup function.
Now, I use a several lookup to get a several results and then I place them
as value and use IF to redefine the result. It is a long way but somehow it
works almost 90% of the times.

Thanks for your help.

 

[JLatham]

Well, I'm glad my part of it ended up working. Remember that all LOOKUP
variants; LOOKUP, VLOOKUP and HLOOKUP always stop looking at the first match
they make, so they can't see multiple possible matches in a list. Also,
unless you specify the 4th parameter in VLOOKUP() and HLOOKUP() as ,FALSE)
then the lists have to be in sequence.