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:

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.
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&"~ ".
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.
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.)

@JB-007 - Xlookup might work, but I'm unsure how. I need to know if every Whole Word present in Cell B1 is listed as a Whole Word inside list A1:A10, including case sensitivity. It's like I need an exact match on every word inside B1, rather than the entire B1 cell. Is that possible? Sorry if I'm complicating things. This is probably one of the more difficult things I've tried to do in Excel, and I'm no expert. — k.cummins, Aug 30 '21 at 19:55
@JB-007 Okay I see the usefulness of xlookup and how that could lead to a solution. Basically, does the contents of cell B1 exist inside A1:A10. But that checks the entire B1 cell... how would I use it to check each word inside B1? — k.cummins, Aug 30 '21 at 19:59
If you want to check that then you'll be returning True for cells with Abc_Xyz I'm afraid!! — JB-007, Aug 30 '21 at 21:39

mark fitzpatrick · Answer 1 · 2021-08-30T20:15:01.790

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

edited Aug 30 '21 at 20:15

answered Aug 30 '21 at 19:20

mark fitzpatrick

3,162
2
11
23

Hi there - see revised soln (my initial approach failed in that it would only match on initial records - e.g. lookup with list length 2 would not return correct value if in row 3 of data list - this is not the case any more thanks to xlookup)... I explored VB soln too but found that the '=' operator was not strong enough to ensure case sensitivity.... – JB-007 Aug 30 '21 at 19:31
@JB-007 I'm still not sure I am following. The statement *"Lets say cell B1 contains the string "Abc Xyz"."* in the OP makes me believe that when B1 contains "Abc Xyz", the formula must result in TRUE if either "Abc" or "Xyz" are in the list A1:A10, but not if they contained merely "abc" or "xyz" or "Ab" or "Bc". Btw - your previous post had some clever pieces. – mark fitzpatrick Aug 30 '21 at 19:41
Hello Mark and JB, thank you for a quick response. Mark's solution is what I was after! Testing this on my end made me realize I need to modify things though... I need there to be matches for ALL whole words inside cell B1, so there needs to be both "Abc" and "Xyz" present in Column A to give a TRUE result. How would you modify this to do that? THANK YOU SO MUCH! – k.cummins Aug 30 '21 at 19:46
OP looking for whole word matches "*search against whole-word matches only, with case sensitivity, *" - so if looking for ABC and xyz then ABC_xyz should return false but ABC *or* xyz (but not both) should return true resp... thanks for compliment re old soln, but like I said, that failed if ABC in row 3 ... this one far more parsimonious... – JB-007 Aug 30 '21 at 19:47
Hi @k.cummins - which version of Excel are you using? – mark fitzpatrick Aug 30 '21 at 19:53
@markfitzpatrick - Office 365 subscription, so I'm guessing the latest? lol – k.cummins Aug 30 '21 at 20:04
@k.cummins - yep, that's always the latest (so long as you're current on billing ;-). I will post a LET that should do the equivalent of AND, so that all words in B1 must be present in the List to qualify as TRUE. Hope that's what you are looking for. – mark fitzpatrick Aug 30 '21 at 20:07
1

Anything regarding `FILTERXML()` is a feast for the eye! – JvdV Aug 30 '21 at 20:27

JB-007 · Answer 2 · 2021-08-30T19:48:21.397

1

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...

edited Aug 30 '21 at 19:48

answered Aug 30 '21 at 18:49

JB-007

2,156
1
6
22

JvdV · Answer 3 · 2021-08-31T06:34:29.713

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.

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

3 Answers3