Match comma delimited values in cell against list and match each value

[Slawbawn]

Hello,

I feel like this is probably one of those things that really simple and probably staring me in the face, but I'm just not catching onto it.

We have a spreadsheet which contains an IP column. This column could contain anything from 1+ IP address, column delimited, for each IP that the row applies to. We have another sheet which has a list of each individual IP, and their corresponding hostname, with one matching IP and host for each row. I'm trying to work out how to take the comma delimited IPs, match each one against individual IPs from the second sheet, and then add the corresponding hostnames, also comma delimited, into the hostname column. So, in essence, take multiple values from Sheet1:ColumnA, match against individual values in Sheet2:ColumnA and input the values from Sheet2:ColumnB to Sheet1:ColumnB.

Book1
	B
3
Results

Book1
	B
7
Hosts

Hopefully the two tables above demonstrate how the data is shown. I've included an example of what a complete one would look like in the first row on sheet 1.

 

[Slawbawn]

Book1
	A	B
1	IPs	Hostnames
2	1.1.1.1, 2.2.2.2, 3.3.3.3.3	Host1, Host2, Host3
3	4.4.4.4, 5.5.5.5
4	3.3.3.3, 4.4.4.4, 1.1.1.1
Results

Book1
	A	B
1	IP	Hostname
2	1.1.1.1	Host1
3	2.2.2.2	Host2
4	3.3.3.3	Host3
5	4.4.4.4	Host4
6	5.5.5.5	Host5
Hosts

Just realised I did the tables completely wrong

 

[Joe4]

Welcome to the Board!

Just to see if I understand this correctly, you want to populate column B on Sheet1.
Though in your example you only did the first row, when complete, should it look like this?

IPs	Hostnames
1.1.1.1, 2.2.2.2, 3.3.3.3.3	Host1, Host2, Host3
4.4.4.4, 5.5.5.5	Host4, Host5
3.3.3.3, 4.4.4.4, 1.1.1.1	Host3, Host4, Host1

Are you OK with a VBA solution?

 

[Slawbawn]

Yep thats the one. VBA will be fine

 

[Joe4]

OK, I created my own user defined function (UDF) in VBA to do this.
The code looks like this:

Function IPLookup(ips As String, rng As Range) As String
'   Looks up IP addresses and returns string of hosts
'   Input Variables:
'       - ips = string of IP addresses to look up
'       - rng = range of lookup table

    Dim ipArr() As String
    Dim i As Long
    Dim ip As String
    Dim host As String
    Dim temp As String
    
'   Split list of ips into an array
    ipArr = Split(ips, ",")
    
'   Loop through list of ips
    For i = LBound(ipArr) To UBound(ipArr)
'       Get particular ip address
        ip = Trim(ipArr(i))
'       Lookup ip address in lookup table
        On Error GoTo err_bad_ip
        host = Application.WorksheetFunction.VLookup(ip, rng, 2, 0)
'       Add host to string
        temp = temp & host & ", "
        On Error GoTo 0
    Next i
    
'   Return string
    If Len(temp) > 0 Then
        IPLookup = Left(temp, Len(temp) - 2)
    End If

    Exit Function
    
    
err_bad_ip:
'error handler to handle bad ips
    host = "BAD IP"
    Err.Clear
    Resume Next

End Function

Note that if there is an IP addres for which there is no match, it will return "BAD IP" for that one.

Then you would just use it like any other function in Excel.
So, if you wanted to look up the value in cell A2, you would enter a formula like this:

=iplookup(A2,Hosts!A$2:B$6)