Formula Not Returning Expected Value

[meppwc]

Can someone tell me why this formula is returning "FALSE" opposed to "YES" or "NO" ?
I have a feeling the brackets are incorrect

=IF(AND(($F$2>=A4),($C$2<=B4)),OR(IF(AND(($F$2>=A5),($C$2<=B5)),"YES","NO")))

 

[TriAxisZero]

you don't need the second IF statement

=IF(OR(AND($F$2>=A4,$C$2<=B4),AND($F$2>=A5,$C$2<=B5)),"YES","NO")

*Edited, missed a closing parentheses on the OR

 

[Joe4]

Your parentheses are not in the correct place. You completely close out the AND formula, so the OR part of your formula is actually in argument 2 (what to return if TRUE), not argument 1 (part of what you are checking).

Please explain ALL the conditions that you want to check, in plain English (so we can accurately understand how you would like them grouped).
(I could "guess", but I do not like to assume I know what your intention is.)

 

[meppwc]

The formula that TriAxisZero sent =IF(OR(AND($F$2>=A4,$C$2<=B4),AND($F$2>=A5,$C$2<=B5)),"YES","NO")
works properly
Ultimately I am trying include conditions that not only include A4, B4 and A5, B5 as in the formula above
But also include all the way up to A300, B300

I know that will make for a very long formula but I do not know any other way to accomplish it.

 

[Joe4]

The formula that TriAxisZero sent =IF(OR(AND($F$2>=A4,$C$2<=B4),AND($F$2>=A5,$C$2<=B5)),"YES","NO")
works properly

I am glad he guessed properly.

Ultimately I am trying include conditions that not only include A4, B4 and A5, B5 as in the formula above
But also include all the way up to A300, B300

I know that will make for a very long formula but I do not know any other way to accomplish it.

I doubt that is going to be possible (at least in the way that you have proposed). There are limits to the length of formulas and how many conditions you can add.

I think you would be better off to post a sample of your data, and explain (in plain English), exactly what you want this formula to do/check.
There may be more efficient ways to do what you want, if we can see what you are working with and completely understand what you are trying to accomplish.

 

[meppwc]

Thanks Joe4

I have the following ranges of IP addresses that I will search against
(For example purposes I am just going to provide the first 50 ranges)
Note that these ranges are in columns A and B
The remainder of my message is below these ranges:

10.126.192.21 10.126.192.22
10.126.193.21 10.126.193.22
10.126.194.22 10.126.194.23
10.126.195.21 10.126.195.26
10.126.196.22 10.126.196.23
10.126.197.25 10.126.197.28
10.126.198.22 10.126.198.26
10.126.199.22 10.126.199.24
10.126.201.22 10.126.201.23
10.126.202.21 10.126.202.23
10.126.203.24 10.126.203.25
10.126.204.22 10.126.204.23
10.126.205.27 10.126.205.30
10.126.206.21 10.126.206.24
10.126.207.25 10.126.207.27
10.126.208.21 10.126.208.23
10.126.209.21 10.126.209.22
10.126.211.21 10.126.211.23
10.126.212.21 10.126.212.23
10.126.213.21 10.126.213.22
10.126.214.21 10.126.214.22
10.126.215.24 10.126.215.25
10.126.216.24 10.126.216.25
10.126.217.22 10.126.217.23
10.126.218.21 10.126.218.22
10.127.64.21 10.127.64.22
10.127.65.21 10.127.65.22
10.127.66.22 10.127.66.35
10.127.67.21 10.127.67.22
10.127.68.23 10.127.68.24
10.127.69.22 10.127.69.23
10.127.70.23 10.127.70.24
10.127.71.21 10.127.71.22
10.127.72.21 10.127.72.22
10.127.73.21 10.127.73.24
10.127.74.21 10.127.74.23
10.127.75.22 10.127.75.29
10.127.76.22 10.127.76.23
10.127.77.21 10.127.77.31
10.127.78.21 10.127.78.22
10.127.79.21 10.127.79.22
10.127.80.21 10.127.80.22
10.127.81.21 10.127.81.23
10.127.82.21 10.127.82.22
10.127.83.29 10.127.83.30
10.127.85.24 10.127.85.25
10.127.86.21 10.127.86.22
10.127.87.21 10.127.87.23

F3 through F15 are IP addresses that I want to search for to see if they fall in any of the 50 ranges that I listed above
If found, the formulas that will be in G3 through G15 will return YES. If not found, the formulas will return NO.
Below are the addresses that are in F3 through F15

10.126.193.21
10.126.195.21
10.126.197.26
10.36.134.38
10.36.142.32
10.36.146.33
10.37.148.188
10.36.144.36
10.126.192.21
10.126.193.21
10.126.194.22
10.126.195.21
10.126.196.22

 

[Joe4]

I might consider converting them to numbers (maybe behind the scenes in another sheet, to make the calculations easy).
There are four parts to each address. Is is true that each part can never be more than 3 characters long?

If so, then I would conver them to numeric entries like:

[TABLE="width: 86"]
<colgroup><col></colgroup><tbody>[TR]
[TD="align: right"]10126193021[/TD]
[/TR]
[TR]
[TD="align: right"]10126195021[/TD]
[/TR]
[TR]
[TD="align: right"]10126197026[/TD]
[/TR]
[TR]
[TD="align: right"]10036134038[/TD]
[/TR]
[TR]
[TD="align: right"]10036142032[/TD]
[/TR]
[TR]
[TD="align: right"]10036146033[/TD]
[/TR]
[TR]
[TD="align: right"]10037148188
...[/TD]
[/TR]
</tbody>[/TABLE]

Then, you can simply check to see if the number falls between the range.

 

[Joe4]

Here is a little Custom Function that I just whipped up that will convert an IP address to a number:

Function IPtoNum(ip As String) As Double

    Dim arr() As String
    Dim i As Long
    Dim total As Double
    
    arr = Split(ip, ".")
    For i = LBound(arr) To UBound(arr)
        Select Case i
            Case 0
                total = total + (arr(0) * 1000000000)
            Case 1
                total = total + (arr(1) * 1000000)
            Case 2
                total = total + (arr(2) * 1000)
            Case 3
                total = total + arr(3)
        End Select
    Next i
    
    IPtoNum = total
    
End Function

Add this to a standard VBA module in your workbook.
Then you can use it like any other Excel function.
So, if you have an IP address in cell A1, this formula will return the numeric value of it:
=IPtoNum(A1)

 

[meppwc]

I was able to successfully convert the IP addresses to numbers

 

[Joe4]

Great, now all you have to do is to write a COUNTIFS formula that counts how many times a value falls between the start and end values for each IP range.
If it is greater than 0, then you have a "Yes", otherwise you have a "No".

So, if your convertred starting ranges were in column G, and your converted ending ranges were in column H, and the converted value you are looking up is in cell K1, then the formula would look like:

=IF(COUNTIFS(G:G,"<=" & K1,H:H,">=" & K1)>0,"Yes","No")