1

So I have two lists in excel.

The first list is made up of large strings.

The second list is made up of smaller strings.

I need to check the first list, to see if any of the strings in the second list are contained in each cell.

Is there an excel formula for that?

I can look up each one individually with an isnumber formula

=IF(ISNUMBER(SEARCH("*dog*",A1)),"dog","No Dog")

but I have a few hundred to go through, so if I can do something like a VLOOKUP that would be better.

For example if one cell in 1st list contacts "Bored" and the second list contains the first 4 letters of the alphabet (a,b,c,d) then a "true" value" of some such would be fine. Then I'd want to check the second cell in the list, and if that was "Tempest" than a "False" value is what I would need, and so on and so on down the list.

MRDoubleyou
  • 45
  • 1
  • 8
  • 1
    One thing unclear,the smaller substring should be contained in the adjacent (larger) cell or in the entire list? Also, if the latter, you need to think how to react in cases where the substring is contained in multiple strings (cells), because so far that info is missing in your original question – Samuel Hulla Jun 22 '18 at 14:32
  • Just in the cell. For example if one cell in 1st list contacts "Bored" and the second list contains the first 4 letters of the alphabet (a,b,c,d) then a "true" value" of some such would be fine. Then I'd want to check the second cell in the list, and if that was "Tempest" than a "False" value is what I would need, and so on and so on down the list. – MRDoubleyou Jun 22 '18 at 14:41
  • @MRDoubleyou please [edit](https://stackoverflow.com/posts/50990174/edit) your question with the additional info rather than add it in comments. It will make it a lot easier for everyone to see all of it. Also you are not answering Rawrplus' question: what happens if "bored" is in two or more cells in the first list? – cybernetic.nomad Jun 22 '18 at 14:48
  • [tag:vba] solution [here](https://stackoverflow.com/questions/32860792/count-and-highlight-keywords-within-phrases/32878493#32878493) –  Jun 22 '18 at 15:11

2 Answers2

3

With phrases in column A and keywords in column B, in D1 enter the array formula:

=LEN(TEXTJOIN(",",TRUE,IF(ISNUMBER(SEARCH(B$1:B$10,A1)),B$1:B$10,"")))>0

enter image description here

Array formulas must be entered with Ctrl + Shift + Enter rather than just the Enter key. If this is done correctly, the formula will appear with curly braces around it in the Formula Bar.

NOTE:

the core of this formula:

=TEXTJOIN(",",TRUE,IF(ISNUMBER(SEARCH(B$1:B$10,A1)),B$1:B$10,""))

actually lists the key words:

enter image description here

EDIT#1:

If your version of Excel does not support TEXTJOIN() then use this array formula:

=LEN(IFERROR(INDEX(B:B,MATCH(TRUE,ISNUMBER(SEARCH($B$1:$B$10,A1)),0)),""))>0

enter image description here

or this non-array formula:

=SUMPRODUCT(--ISNUMBER(SEARCH($B$1:$B$10,A1)))>0
Gary's Student
  • 95,722
  • 10
  • 59
  • 99
0

Personally I prefer to do a COUNTIF against the range + a cell concatenated with wildcards:

=COUNTIF(A1:A6,CONCATENATE("*"&B1&"*"))

img1

If you need it to return a boolean then you can use:

=IF(COUNTIF(A1:A6,CONCATENATE("*"&B1&"*")) > 0, TRUE, FALSE)

dwirony
  • 5,487
  • 3
  • 21
  • 43