Need help with index/match (possibly) related formula

Hi ,

 

Thanks in advance for your time.

 

I am trying to figure out a formula to help me return data fields that match information in columns. Basically I  have two columns that contain not exact matches (ex: (last four digits of host name and first four digits of serial number) and each Hostname has a corresponding IP address. I am trying to find the corresponding IP address of all hostnames that match serial numbers in a large array of data. You see below the last four digits of the host name match the first four of the serial numbers, so I would like to write a formula that can produce the corresponding IP address for these hostnames. I hope I’m explaining this correctly.

 

 

IP Address Hostname
154.56.27.12 Xnet52AJAXW3-GT7T4C
154.56.27.13 Xnet52AJAXW1234
154.56.27.14 Xnet52AJAXW4567
154.56.27.15 Xnet52AJAXW8901
154.56.27.16 Xnet52AJAXW2345
154.56.27.17 Xnet52AJAXW6789

 

Manufacturer Model Serial Number
Dell 2500 7T4CMN2
Dell 2500 1234PCV2
Dell 2500 4567PCV2
Dell 2500 8901PCV2
Dell 2500 2345PCV2
Dell 2500 6789PCV2

 

What I have done so far was use right and left formulas to produce the digits, then an if statement to see if they match each other. Then I’m trying to write an index/match to produce the serial number, but I can’t get it to work. I’m feeling that there might be an easier way to do this.

 

Thanks so much for your help.

By: Luke McConaghyy

One thought on “Need help with index/match (possibly) related formula

  1. D N Maru

    Hi
    You need to use array formula which are entered using ctrl+shift+enter.
    Suppose You have column A as manufacture, B Model, C serial Number, D is the column where you want corresponding IP address.
    Also, assume G column has IP Address and H has Host name.

    Formula in D2 would look like this ={INDEX(G:H,MATCH(LEFT(C2,4),RIGHT(H:H,4),0),1)}

    There is a special way of writing array formula. Please do contact me if you have any problem.

Leave a Reply

Your email address will not be published. Required fields are marked *