Data matching

Last Edited By Krjb Donovan
Last Updated: Mar 05, 2014 09:24 PM GMT


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:] and Sheet-2 contains only the first two octets of the IP Segment [example:]. On Sheet-2, I need to extract a value located in the section column (B).

Sheet-1 Column-A =

Sheet-2 Column-A = 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 in ColumnA of Sheet-1.

Let me know if this works well for you.


©2018 eLuminary LLC. All rights reserved.