Home

Malfunctional Formula

%3CLINGO-SUB%20id%3D%22lingo-sub-712470%22%20slang%3D%22en-US%22%3EMalfunctional%20Formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-712470%22%20slang%3D%22en-US%22%3E%3CP%3E%3DIFERROR(INDEX(TRIM(ISNUMBER(SEARCH(A1%2CA2%3AA4)))%2C%20MATCH(B1%26amp%3B%22*%22%2CB2%3AB4%2C0))%3DTRIM(A1)%2C%22TRUE%22)%3C%2FP%3E%3CP%3ESo%20B%20Column%20is%20working%20perfectly.%20The%20A%20column%20is%20messing%20up%20though.%20A1%3DDoreen%20and%20A3%3DDoreen%20W%3C%2FP%3E%3CP%3EI'm%20trying%20to%20get%20this%20formula%20to%20return%20true%20because%20the%20word%20Doreen%20is%20in%20the%20cell%20but%20it's%20returning%20false(most%20likely%20because%20of%20the%20W)%20What%20did%20I%20do%20wrong%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-712470%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-712602%22%20slang%3D%22en-US%22%3ERe%3A%20Malfunctional%20Formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-712602%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F73582%22%20target%3D%22_blank%22%3E%40Thomas%20Bryant%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20didn't%20tell%20about%20column%20B%20so%20I%20just%20focused%20on%20column%20A%3A%3C%2FP%3E%3CPRE%3E%3DCOUNTIFS(A2%3AA4%2C%22*%22%26amp%3BA1%26amp%3B%22*%22)%26gt%3B0%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-713968%22%20slang%3D%22en-US%22%3ERe%3A%20Malfunctional%20Formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-713968%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F73582%22%20target%3D%22_blank%22%3E%40Thomas%20Bryant%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20for%20attached%20file%20when%20it%20could%20be%3C%2FP%3E%0A%3CPRE%3E%3DIFERROR(INDEX(LEFT(%24A%242%3A%24A%244%2CSEARCH(%22%20%22%2C%24A%242%3A%24A%244%26amp%3B%22%20%22)-1)%2C%20MATCH(B1%26amp%3B%22*%22%2CB2%3AB4%2C0))%3DLEFT(A1%2CSEARCH(%22%20%22%2CA1%26amp%3B%22%20%22)-1)%2C%22TRUE%22)%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-714145%22%20slang%3D%22en-US%22%3ERe%3A%20Malfunctional%20Formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-714145%22%20slang%3D%22en-US%22%3EIt%20was%20for%20a%20different%20file%20but%20it%20worked%20like%20a%20charm.%20I%20have%205%20different%20lists%20im%20trying%20to%20make%20quick%20to%20search%20but%20they%20all%20have%20different%20formats.%20Most%20have%20been%20easyish%20but%20those%20two%20had%20stumped%20me%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-714361%22%20slang%3D%22en-US%22%3ERe%3A%20Malfunctional%20Formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-714361%22%20slang%3D%22en-US%22%3E%3CP%3EThomas%2C%20so%20far%20so%20good.%20As%20a%20comment%2C%20asking%20the%20question%20it's%20always%20better%20to%20attach%20sample%20file%20where%20possible.%20You%20are%20within%20your%20projects%20and%20some%20things%20which%20are%20obvious%20for%20you%20not%20clear%20for%20us.%20We%20here%20could%20make%20only%20the%20guess%20and%20not%20always%20correct%20guess.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Thomas Bryant
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.

Related Conversations
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
35 Replies
Extentions Synchronization
ChirmyRam in Discussions on
3 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies