Excel - Match cells that contains exact match from list

Question

In my first sheet I have a list which contains the following 10 items in Column A (One on row: A2-A11):

egg
bootleggers egg
egg is good
egg and ham
eggs and hams
bootleggers
eggshells
veggie
reggae

I have a second sheet which contains a table (table name: good_food) with the following items:

egg
ham

I want to find out which items in the first sheet that contains an exact match from the list in the second sheet.

This almost works just this formula:

=SUMPRODUCT(--ISNUMBER(SEARCH(good_food;A2)))>0

Though TRUE is returned on all items:

egg TRUE
bootleggers egg TRUE
egg is good TRUE
eggisgood   TRUE
egg and ham TRUE
eggs and hams   TRUE
bootleggers TRUE
eggshells   TRUE
veggie  TRUE
reggae  TRUE

The formula makes a "contains broad match" instead of an "contains exact match". The items "eggisgood", "eggs and hams", "bootleggers", "eggshells", "veggie" and "reggae" should be FALSE.

I am guessting the MATCH-formula might get this to work but I can't figure out how.

Update: I don't need to match the items in case sensitive.

Answer 1

Use find instead of search for exact and specific words to find

=SUMPRODUCT(--ISNUMBER(FIND(" "&E16:E18&" "," "&B20&" ")))>0

Answer 2

Changed the formula to =SUMPRODUCT(--ISNUMBER(SEARCH(" "&good_food&" ";" "&A2&" ")))>0 which solved it.

Answer 3

Bracket both the criteria and lookup_range in a suitable delimiter.

=ISNUMBER(FIND(" "&B$1&" ", " "&$A2&" "))

This is a case-sensitive lookup¹.

    

---

¹ _{The FIND function is case sensitive. The SEARCH function is not.}