Vlookup IP Address and can Return Yes/No?

[billtadz]

Similar to this old thread

vlookup to return Yes and No values

Is it possible to write a vlookup that will return a value of Yes if the value is true and a value of No if the value is false? I have data in column A on sheet 1 so the lookup value starts in cell A2 and the table array is on sheet 2 in columns A and B so the column index is 2.

www.mrexcel.com

But, I have a string in column A like below, outputs in D & E and two IPs in B&C.

Appreciate for a VB Script if possible, or is there another way aside from vlookup function?

SOURCE	First IP	Second IP	RESULT First IP	RESULT Second IP
TEST 172.26.56.104	172.26.56.104	255.255.255.0	YES	NO
TEST	172.26.56.105	255.255.255.1	NO	NO
TEST 172.26.56.106 255.255.255.2	172.26.56.106	255.255.255.2	YES	YES
TEST 255.255.255.3	172.26.56.107	255.255.255.3	NO	YES

 

[alansidman]

Option Explicit

Sub IPTest()
    Dim i As Long, lr As Long
    lr = Range("A" & Rows.Count).End(xlUp).Row
    Application.ScreenUpdating = False
    For i = 2 To lr
        If InStr(Range("A" & i), Range("B" & i)) > 0 Then
            Range("D" & i) = "Y"
        Else: Range("D" & i) = "N"
        End If
        If InStr(Range("A" & i), Range("C" & i)) > 0 Then
            Range("E" & i) = "Y"
        Else: Range("E" & i) = "N"
        End If
    Next i
    Application.ScreenUpdating = True
    MsgBox "complete"

End Sub

 

[billtadz]

Thank you for the quick response, how about if I add another column for third IP result.

SOURCE	First IP	Second IP	Third IP	RESULT First IP	RESULT Second IP	RESULT Third IP
TEST 172.26.56.104	172.26.56.104	255.255.255.0		YES	NO
TEST	172.26.56.105	255.255.255.1		NO	NO
TEST 172.26.56.106 255.255.255.2	172.26.56.106	255.255.255.2		YES	YES
TEST 255.255.255.3	172.26.56.107	255.255.255.3		NO	YES
TEST 255.255.255.3	172.26.56.107	255.255.255.3		NO	YES
TEST 172.26.56.106 255.255.255.2 SAMPLE 13.20.189.10	172.26.56.106	255.255.255.2	13.20.189.10	YES	YES	YES
TEST 172.26.56.106 SAMPLE 13.20.189.10	172.26.56.106	255.255.255.2	13.20.189.10	YES	NO	YES
TEST 255.255.255.2 SAMPLE 13.20.189.10	172.26.56.106	255.255.255.2	13.20.189.10	NO	YES	YES
TEST 172.26.56.106 255.255.255.2 SAMPLE	172.26.56.106	255.255.255.2	13.20.189.10	YES	YES	NO

 

[alansidman]

Follow the same format as shown for the first two items. But change the ranges over one column.

Option Explicit

Sub IPTest()
    Dim i As Long, lr As Long
    lr = Range("A" & Rows.Count).End(xlUp).Row
    Application.ScreenUpdating = False

    For i = 2 To lr
        If InStr(Range("A" & i), Range("B" & i)) > 0 Then
            Range("E" & i) = "Y"
        Else: Range("E" & i) = "N"
        End If
        If InStr(Range("A" & i), Range("C" & i)) > 0 Then
            Range("F" & i) = "Y"
        Else: Range("F" & i) = "N"
        End If
         If InStr(Range("A" & i), Range("D" & i)) > 0 Then
            Range("G" & i) = "Y"
        Else: Range("G" & i) = "N"
        End If
    Next i
    Application.ScreenUpdating = True
    MsgBox "complete"

End Sub

 

[billtadz]

Ok got it, I want to highlight the cell of 'NO' to the colour red. How we modify the VB?

Option Explicit

Sub IPTest()
    Dim i As Long, lr As Long
    lr = Range("A" & Rows.Count).End(xlUp).Row
    Application.ScreenUpdating = False
    For i = 2 To lr
        If InStr(Range("A" & i), Range("B" & i)) > 0 Then
            Range("E" & i) = "YES"
        Else: Range("E" & i) = "NO"
        End If
        If InStr(Range("A" & i), Range("C" & i)) > 0 Then
            Range("F" & i) = "YES"
        Else: Range("F" & i) = "NO"
        End If
        If InStr(Range("A" & i), Range("D" & i)) > 0 Then
            Range("G" & i) = "YES"
        Else: Range("G" & i) = "NO"
        End If
    Next i
    Application.ScreenUpdating = True
    MsgBox "complete"

End Sub

 

[billtadz]

and I wanted also the blank IP to return as blank in result.

 

[alansidman]

This will turn No cells Red. Still working on other issue. Bit sticky for me.

Sub IPTest()
    Dim i As Long, lr As Long
    lr = Range("A" & Rows.Count).End(xlUp).Row
    Application.ScreenUpdating = False

    For i = 2 To lr
        If InStr(Range("A" & i), Range("B" & i)) > 0 Then
            Range("E" & i) = "Y"
        Else: Range("E" & i) = "N"
        Range("E" & i).Interior.ColorIndex = 3
        End If
        If InStr(Range("A" & i), Range("C" & i)) > 0 Then
            Range("F" & i) = "Y"
        Else: Range("F" & i) = "N"
        Range("F" & i).Interior.ColorIndex = 3
        End If
        If InStr(Range("A" & i), Range("D" & i)) > 0 Then
            Range("G" & i) = "Y"
        Else: Range("G" & i) = "N"
        Range("G" & i).Interior.ColorIndex = 3
        End If
    Next i
    Application.ScreenUpdating = True
    MsgBox "complete"

End Sub

 

[Peter_SSs]

is there another way aside from vlookup function?

You could use standard worksheet functions as below and Conditional Formatting to highlight.

20 11 22.xlsm
	A	B	C	D	E	F	G
1	SOURCE	First IP	Second IP	Third IP	RESULT First IP	RESULT Second IP	RESULT Third IP
2	TEST 172.26.56.104	172.26.56.104	255.255.255.0		YES	NO
3	TEST	172.26.56.105	255.255.255.1		NO	NO
4	TEST 172.26.56.106 255.255.255.2	172.26.56.106	255.255.255.2		YES	YES
5	TEST 255.255.255.3	172.26.56.107	255.255.255.3		NO	YES
6	TEST 255.255.255.3	172.26.56.107	255.255.255.3		NO	YES
7	TEST 172.26.56.106 255.255.255.2 SAMPLE 13.20.189.10	172.26.56.106	255.255.255.2	13.20.189.10	YES	YES	YES
8	TEST 172.26.56.106 SAMPLE 13.20.189.10	172.26.56.106	255.255.255.2	13.20.189.10	YES	NO	YES
9	TEST 255.255.255.2 SAMPLE 13.20.189.10	172.26.56.106	255.255.255.2	13.20.189.10	NO	YES	YES
Check IP addresses

Cell Formulas
Range	Formula
E2:G9	E2	=IF(B2="","",IF(ISNUMBER(FIND(" "&B2&" "," "&$A2&" ")),"YES","NO"))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
E2:G9	Expression	=E2="NO"	text	NO