Using COUNTIF to match a String in a StringArray to a list

%3CLINGO-SUB%20id%3D%22lingo-sub-2818038%22%20slang%3D%22en-US%22%3EUsing%20COUNTIF%20to%20match%20a%20String%20in%20a%20StringArray%20to%20a%20list%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2818038%22%20slang%3D%22en-US%22%3E%3CP%3ERight%20now%2C%20I%20have%20a%20list%20of%20keywords%20on%20one%20sheet%2C%20and%20on%20another%20sheet%20I%20have%20a%20few%20columns%20dedicated%20to%20the%20names%20of%20majors.%20My%20current%20conditional%20formula%20will%20fill%20the%20majors%20cells%20green%20if%3A%20COUNTIF%20uses%20the%20list%20of%20keywords%20and%20tries%20to%20match%20the%20current%20cell%20to%20one%20of%20the%20keywords.%20So%20if%20'Journalism'%20is%20the%20cell%2C%20the%20formula%20goes%20into%20the%20list%20on%20the%20other%20sheet%2C%20sees%20that%20'Journalism'%20is%20an%20entry%2C%20and%20color%20codes%20it%20accordingly.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMy%20problem%20is%20that%20in%20more%20complicated%20cells%20like%20'Early%20Childhood%20Education'%2C%20the%20formula%20sends%20it%20as%20a%20whole%20and%20does%20not%20match%20the%20'Education'%20in%20the%20full%20phrase%20to%20the%20'Education'%20in%20the%20list%20of%20key%20terms.%20My%20question%20is%2C%20%3CU%3Eis%20it%20possible%20for%20the%20formula%20to%20pick%20apart%20the%20cell%20to%20match%20it%20with%20the%20list%3C%2FU%3E%2C%20maybe%20taking%20it%20as%20an%20array%20of%20words%20and%20matching%20one%20of%20them%20to%20the%20list%3F%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22mystiqdreamer_3-1633533255933.png%22%20style%3D%22width%3A%20619px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F315627i1E314F5204BEE329%2Fimage-dimensions%2F619x39%3Fv%3Dv2%22%20width%3D%22619%22%20height%3D%2239%22%20role%3D%22button%22%20title%3D%22mystiqdreamer_3-1633533255933.png%22%20alt%3D%22mystiqdreamer_3-1633533255933.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22mystiqdreamer_1-1633533170079.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F315625iA617EB12EFBC6FBD%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22mystiqdreamer_1-1633533170079.png%22%20alt%3D%22mystiqdreamer_1-1633533170079.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22mystiqdreamer_2-1633533194923.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F315626i1CA9BCB3BB2E8D29%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22mystiqdreamer_2-1633533194923.png%22%20alt%3D%22mystiqdreamer_2-1633533194923.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2818038%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2818195%22%20slang%3D%22en-US%22%3ERe%3A%20Using%20COUNTIF%20to%20match%20a%20String%20in%20a%20StringArray%20to%20a%20list%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2818195%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1177241%22%20target%3D%22_blank%22%3E%40mystiqdreamer%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhy%20don't%20you%20adjust%20your%20list%20of%20desirable%20majors%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Right now, I have a list of keywords on one sheet, and on another sheet I have a few columns dedicated to the names of majors. My current conditional formula will fill the majors cells green if: COUNTIF uses the list of keywords and tries to match the current cell to one of the keywords. So if 'Journalism' is the cell, the formula goes into the list on the other sheet, sees that 'Journalism' is an entry, and color codes it accordingly. 

 

My problem is that in more complicated cells like 'Early Childhood Education', the formula sends it as a whole and does not match the 'Education' in the full phrase to the 'Education' in the list of key terms. My question is, is it possible for the formula to pick apart the cell to match it with the list, maybe taking it as an array of words and matching one of them to the list?

mystiqdreamer_3-1633533255933.png

mystiqdreamer_1-1633533170079.png

mystiqdreamer_2-1633533194923.png

 

3 Replies

@mystiqdreamer 

Why don't you adjust your list of desirable majors?

 

@Detlef Lewin That's what I've done in the meantime; I copy/pasted the majors that popped up into my list so the function would detect them. Only thing about that is when I reach new majors that I want as part of the desirable list, they'd have to be manually entered as well, rather than having a portion of it be auto-detected by what I'm looking for. Doing it manually isn't so inconvenient, but it does suck as I have 50+ more colleges to research and some majors are technically the same but called slightly different things.

 

It's definitely an option that solves the problem, but it doesn't satisfy my c#-code-brain thinking :'3 Thanks for the suggestion.

@mystiqdreamer 

It's probably possible to create such a formula but it is also probable that there will be problems ahead: false positives/negatives or different delimiters.

So you might still end up editing your list of desirable majors.