Data matching

QuestionEdit

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.

AnswerEdit

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.