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

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.