Search multiple cells for data "containing" a text string...then return the information from that cell

[Hat4Life]

I am trying to figure out if there is a formula that will let me search multiple contiguous cells for a cell that contains a specific text string. If that value is found, I need the formula to display that text string.

Example: I have 27 cells in a row. Each cell contains a unique product SKU. I want to search all 27 cells and return the value of the cell that contains the text string "HD"

I tried: =MATCH("*-HD*",CI17:CM17,0), but that did not work. Any suggestions would be greatly appreciated.

 

[Cubist]

Try:

=FILTER(A2:G2,ISNUMBER(SEARCH("HD",A2:G2)),"Not found")

 

[Hat4Life]

Try:

=FILTER(A2:G2,ISNUMBER(SEARCH("HD",A2:G2)),"Not found")

That worked exactly how I was hoping something would! Thank you. I had to search 27 cells and knew I could not embed that many "if" formulas in one cell.