Malfunctional Formula

Copper Contributor

=IFERROR(INDEX(TRIM(ISNUMBER(SEARCH(A1,A2:A4))), MATCH(B1&"*",B2:B4,0))=TRIM(A1),"TRUE")

So B Column is working perfectly. The A column is messing up though. A1=Doreen and A3=Doreen W

I'm trying to get this formula to return true because the word Doreen is in the cell but it's returning false(most likely because of the W) What did I do wrong?

4 Replies

@Thomas Bryant 

You didn't tell about column B so I just focused on column A:

=COUNTIFS(A2:A4,"*"&A1&"*")>0

@Thomas Bryant 

 

If for attached file when it could be

=IFERROR(INDEX(LEFT($A$2:$A$4,SEARCH(" ",$A$2:$A$4&" ")-1), MATCH(B1&"*",B2:B4,0))=LEFT(A1,SEARCH(" ",A1&" ")-1),"TRUE")

 

It was for a different file but it worked like a charm. I have 5 different lists im trying to make quick to search but they all have different formats. Most have been easyish but those two had stumped me

Thomas, so far so good. As a comment, asking the question it's always better to attach sample file where possible. You are within your projects and some things which are obvious for you not clear for us. We here could make only the guess and not always correct guess.