Excel Formula: Search a Column of Strings to Match Another Column

最新推荐文章于 2023-06-30 19:23:09 发布

原创最新推荐文章于 2023-06-30 19:23:09 发布 · 102 阅读

CC 4.0 BY-SA版权

本文介绍了一种在Excel中快速搜索并匹配IP地址的方法。通过使用MATCH函数结合通配符，可以实现从一个列中查找是否包含另一个列中的IP地址，并定位到具体的行号。

Once a while, I have to work on Excel sheet manually. Today I have to search exported hundreds of ip addresses in a pre-defined excel spreadsheet to see if there is a match. It seems a easy work, but it took me almost an hour to find right formula.

Column O includes all exported ip addresses. I have to manually search if those column O’s ip addresses are appearing in column B’s text. If yes, which row is it?

Formula is set at Column N.

=MATCH(“*”&(O6)&”*”,B:B,0)

As you can see from cell N6, the number is 7, which means the text in B7 includes O6’s string.
N12’s number is 5, which means the test in B5 includes O12’s string.