Excel - Search Cell for String From List, WITH Case Sensitivity AND Wildcards

Question

I've been struggling to make this work. I can find bits and pieces of different formulas online but haven't been able to get what I need.

So, I have a list of cells that each contain a word, lets say in cells A1:A10. In cell B1, I have a string of text. I want to search cell B1 for any of the words in the A1:A10 list, BUT I need it to search WITH Case Sensitivity, AND ALSO allow the use of wildcards (so I can get whole word matches only). How is this possible?

Here is a random example to explain my logic: Lets say cell B1 contains the string "Abc Xyz". Within the A1:A10 list, there are the strings "Ab" and "Bc". I want this search to return false, because a whole word from the list wasn't found in B1. But if "Abc" or "Xyz" was added to the list, then the search would return true because a whole word was found. So far, I've found that:

1. The FIND function is Case sensitive, so it won't register a match with "Bc" which is good, but it will with "Ab". So with "Ab" I get a true when I want a false. And it can't use wildcards, see next comment.
2. The SEARCH function isn't Case Sensitive, so it finds "Bc", so I get a true when I want a false. BUT it allows me to use wildcards, so I can search "Ab~ " so it looks for a space only after the search term, which gives me the "Ab" false when I need it. I might have the syntax wrong, but the search-for range would be A1:A10&"~ ".
3. The EXACT function doesn't work because "Abc" and "Xyz" are not exact matches to "Abc Xyz". So I get a false when I want a true.
4. The MATCH function doesn't work because "Abc Xyz" will never be in the A1:A10 list in a single cell.

Any help you all can provide is GREATLY appreciated. Been wracking my brain on this for hours.

(Edit: In a nutshell, I'm looking for a way to search against whole-word matches only, with case sensitivity, from a list of search terms.)

Answer 1

Kudo's on a well written question and the amount of research you have given it.

With just a range of 10 or so cells you may just use TEXTJOIN() to create a string we can use a a parents-text-attribute inside a valid xml-string. Try:

Formula in D1:

=ISERROR(FILTERXML("<t>"&TEXTJOIN(",",0,,A1:A3,"<s>")&SUBSTITUTE(B1," ","</s><s>")&"</s></t>","//s[not(contains(../text(),concat(',',.,',')))]"))

• "<t>"&TEXTJOIN(",",0,,A1:A3,"<s>")&SUBSTITUTE(B1," ","</s><s>")&"</s></t>": Create a valid XML-string. With the given example this returns: <t>,Xyz,Bc,Abc,<s>Abc</s><s>Xyz</s></t>
• "//s[not(contains(../text(),concat(',',.,',')))]": Now let's use a valid xpath expression inside FILTERXML() to return an array of nodes where, when each individual node is concatenated with commas, the text attribute of the parent won't contain this text.
• ISERROR(): The part that will return you a TRUE if FILTERXML() can't return any nodes (thus gives an error) or FALSE if there is any node that is validated by the given XPATH-syntax.

If FILTERXML() does interest you, and you would like to know how to return arrays from given strings, this could be an interesting read.

Answer 2

Original Answer - based on OR logic

I may not have fully understood the question, so apologies in advance if this is off the mark. Seeing JB's answer creates more doubt, but here goes:

=OR(ISNUMBER(FIND(TRANSPOSE(FILTERXML("<main><sub>"&SUBSTITUTE(B1," ","</sub><sub>")&"</sub></main>","//sub")),
                  A1:A10 ) ) )

I expanded the test set to 16 to evaluate different scenarios. B1 contains "Abc Xyz" and the list contains a semi-random list of names with "Ab", "Xy, "Abc", etc., as well as blanks. mixed in for testing.

requires: Excel 365, Excel 2019, Excel 2016, or Excel 2013

New Answer based on AND logic

This is more complex (at least to my knowledge) because it cannot simply OR all of the results of the array into a single outcome. Applying an AND would result in FALSE in all cases, so we need to create an array that has columns that are effectively ORed, column-wise and then those results are ANDed row-wise. This formula will do it:

=LET( list, A1:A10,
      testString, B1,
       testArray, IFERROR( FIND( TRANSPOSE( FILTERXML( "<main><sub>" & SUBSTITUTE( testString, " ", "</sub><sub>" ) & "</sub></main>", "//sub" ) ),
                           list ),  0 ),
       ones, SIGN( SEQUENCE( 1, ROWS( testArray ) ) ),
       AND( MMULT( ones, testArray ) ) )

Where the list is your range of words (in A1:A10) and testString is the string that you want to test in B1 in your example. You can see the two methods side-by-side here with "abc Xyz" as the test case in B1.

requires: Excel 365

Answer 3

Here/screenshots refer:

assumes Office 365 compatible version of Excel

=XLOOKUP(F2:F3,B2:B5,C2:C5,"",0,1)

if no Office 365 then vlookup should also work...