# Substitute conditional IF function

Closed
David - Sep 12, 2009 at 10:18 AM
David Scott Posts 1 Registration date Friday September 11, 2009 Status Member Last seen September 13, 2009 - Sep 13, 2009 at 11:00 AM
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(between -25 to 0), B1 and C1 (between 0 to 31) etc...
If it does found, it will display the found value in a cell in question.

Thanks.
Related:

## 2 responses

venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 811
Sep 12, 2009 at 08:58 PM
You did not give complete informational. If a cell in range A1:AA77 is between A1 and B1 what should I do
quote
If it does found, it will display the found value in a cell in question.
unquote
what is meant by "display" and "cell in question"

tell me this
1.suppose F7 is found to be between a1 and B1 what should I do???????
2. suppose more than one cell in A7:AA7 which are between a1 and b1 what should I do with all these cells

the macro I have given below will find the cell in A7:AA7 and give its address.

It will continue to loop with all the cells in A1:AA7 and
then loop next
again start finding out whether any cell in A7:AA7 is betwen B1 and C1 and so on till all the cells are exhausted
You modify the macro to suit ou.

```Sub test()
Dim rng As Range, c As Range, rng1 As Range, c1 As Range
Dim x1, x2
With Worksheets("sheet1")
Set rng = Range(.Range("a1"), .Range("a1").End(xlToRight)).Offset(0, -1)
For Each c In rng
x1 = c.Value
x2 = c.Offset(0, 1).Value
x1 = WorksheetFunction.Min(x1, x2)
x2 = WorksheetFunction.Max(x1, x2)
Set rng1 = .Range("a7:aa7")
For Each c1 In rng1
If c1 >= x1 And c1 <= x2 Then
End If
Next c1
Next c
End With
End Sub ```
David Scott Posts 1 Registration date Friday September 11, 2009 Status Member Last seen September 13, 2009
Sep 13, 2009 at 11:00 AM
Sorry for failing short of expanding the detail.
1 If it does found, it will display the found value in a cell in question.

In our sample we have:
A1 = -25, B1 = 0, and C1 = 31

Now, suppose from A7 to AA7 contains the following value:
-20, -7, 12, 15, 25, and 31.

Let said we test in cell B7, C7, D7, and E7 for the value between A1 and B1.
Thus, we would have -20 in cell B7, -7 in C7, and so on.

Let we test the result of B1 to C1 in cell B8, C8, D8, E8, and so on.
In this case, we would have a value 12 in B8, 15 in C8, 25 in D8, 31 in E8, and so forth.

Again sorry.
Thanks.