Substitute conditional IF function Closed

Question

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.

venkat1926 · Answer

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
MsgBox c1.Address
End If
Next c1
Next c
End With
End Sub

David Scott · Answer

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.

Substitute conditional IF function

2 responses

Similar discussions

Subscribe To Our Newsletter!