I am looking for aformula to match two sets of values located on two separate worksheets. Sheet-1 contains the full IP Address of a computer [example: 192.168.1.2] and Sheet-2 contains only the first two octets of the IP Segment [example: 192.168.0.0/24]. On Sheet-2, I need to extract a value located in the section column (B).
Sheet-1 Column-A = 192.168.1.2
Sheet-2 Column-A = 192.168.0.0/24 Column-B = Fort Worth, TX
Your assistance is appreciated.
You can use the MATCH function with a wildcard inside a INDEX function to return the value in column B of Sheet-2.
Example:
=INDEX(Sheet2!B:B,MATCH(LEFT(A1,8)&"*",Sheet2!A:A,0),1)
where: A1 contains 192.168.1.2 in ColumnA of Sheet-1.
Let me know if this works well for you.
Advertisement