# Need help with index/match (possibly) related formula

Hi ,

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.