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")))

 

[Joe4]

I made sure that the From, To and Search numbers are all valid numbers. I did a =VALUE(X) against all of them and used the resulting data

If these formulas:
=ISNUMBER(S3)
=ISNUMBER(Q4)
=ISNUMBER(R4)
all returned false, then those values are not, in fact, numbers (but rather text).
That is what the ISNUMBER function does; it asks, is this entry actually numeric (and not a number entered as text)?.

I did a =VALUE(X) against all of them and used the resulting data

How exactly did you do this?
There must be an issue in how you how tried to apply it.

 

[meppwc]

I tried again and used the defined function that you provided
put =IPtoNum(A3) in a cell and it returned #NAME ?

 

[Joe4]

In which module in VBA did you place the code?
Did you create a new module in your current workbook, and add it there?
Or did you add it to one of the pre-existing Workbook or Sheet modules (you do NOT want to put it there, it won't be able to be seen from all places).

See this here if you are not sure how to do that: https://www.ablebits.com/office-addins-blog/2013/12/06/add-run-vba-macro-excel/

 

[Joe4]

Here is proof that the COUNTIFS will work if the entries are numeric.
This sets up the exact example you laid out for us, and enters the formula, which returns "Yes".
So run this code on any blank sheet, and you will see how it should work:

Sub Test()

    Columns("Q:S").NumberFormat = "0"
    Range("S3") = 1012619321
    Range("Q4") = 1012619321
    Range("R4") = 1012619322
    Columns("Q:S").EntireColumn.AutoFit
    Range("D3").FormulaR1C1 = _
        "=IF(COUNTIFS(C[13],""<="" & RC[15],C[14],"">="" & RC[15])>0,""Yes"",""No"")"

End Sub

 

[meppwc]

Hi Joe4.............I am going to put this effort on hold for a couple of days due to my work load. Needless to say I am not familiar with VBA functions so I will have to take the time to learn what is needed to properly use what you have provided. Hoping to get back on this at the beginning of next week if not tomorrow. Thanks again so much

 

[Joe4]

OK. The good news is that you really do not need to know much about VBA in order to run the code I gave you. The only tricky part is to make sure that you put it in the right place. The link I provided walks you through how to do that.

The User Defined Function (UDF) I provided works like any other Excel function, once you paste that code into a VBA module.
For the other code I provided, you would just drop that into a VBa module, and then run it from the Macro menu on the worksheet.

 

[meppwc]

Joe4

I decided to start from scratch with a brand new worksheet. And you have provided works as expected. Thank you so much for your willingness to share your knowledge. This tool will really help alot. I work for a large corporation that is re configuring IP addresses as two large companies merge.
By inputting the new addresses into this tool to see if they are already covered in a existing range of IP addresses is essential
This tool makes that type of lookup so quick.
Very appreciative.

 

[Joe4]

You are welcome!