Identify 1 IP address from a cell with multiple IPs match against legend of IP segments

[suchetherrah]

Hi,

Would like to find a formula to Identify 1 IP address from a cell which has multiple IPs match against the legend of IP segments.

Can anyone assist for a solution. Please refer to a attached file. Thank you.

Chitra

[jason.b75]

Try this array formula in B4, copy down as needed.

=IFERROR(TRIM(MID(SUBSTITUTE(A4," ",REPT(" ",50)),LOOKUP(1E+100,SEARCH($A$10:$A$13,SUBSTITUTE(A4," ",REPT(" ",50)))),50)),"No Match")

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

[suchetherrah]

Hi Jason,

It working when the 3 set of IP is 2 digit but for 3 digit. Example 11.11.16 (working) but if it's 11.11.161 (not working)
Please refer to example for your reference.


Regards
Chitra

[jason.b75]

The formula was not checking the number of digits, only that it was a match for what was found.

11.111.169.38 was being matched to 11.111.16 in the segment list.

With A6, there are 3 possible matches to 11.12.16, the way I had set up the formula, it would always return the first match in this scenario, even if it was not the correct one.

11.12.161.48
11.12.16.52
11.12.160.48
11.12.65.48

This revised formula gives the correct results in the sample file. Any blank cells in the I.P. segment table will cause the formula to fail. I've used a dynamic range to find the last I.P. address in the table, but if you have any blanks in the middle then they will cause problems.

=IFERROR(TRIM(MID(SUBSTITUTE(A4," ",REPT(" ",50)),LOOKUP(1E+100,SEARCH($A$17:INDEX($A:$A,MATCH("ZZZZ",$A:$A))&".",SUBSTITUTE(A4," ",REPT(" ",50)))),50)),"No Match")