Help with Text search and returning cell ref in if statement

[maximillianrg]

Hello Excel Masters

While Power Query with Fuzzy lookup is great for a lot of work I'm doing, I'm hoping for some help with a searching formula

Sheet 1 Cell A1 = "cat"
Sheet 2 Column A cells A1 - A2000 contain text that ranges from 1-15 words

In Sheet 1 cell A1 I'm looking for a formula that would do the following:

1. Search for the word in Sheet 1 cell A1 in Sheet 2 Column A
2. If no hits are found return 0
3. If more than one instance is found return "Multiple"
4. If only once instance is found return the cell reference that it was found in

Is that possible?

 

[hrayani]

Hi, Try this

=IF(COUNTIF(Sheet2!A1:A2000,"*"&A1&"*")=0,0,IF(COUNTIF(Sheet2!A1:A2000,"*"&A1&"*")=1,CELL("address",INDEX(Sheet2!A1:A2000,MATCH("*"&A1&"*",Sheet2!A1:A2000,0))),IF(COUNTIF(Sheet2!A1:A2000,"*"&A1&"*")>1,"Multiple")))

 

[maximillianrg]

That Worked Amazingly well - thank you so much Hrayani

 

[hrayani]

That Worked Amazingly well - thank you so much Hrayani

You are welcome & thanks for the feedback