How do you retrieve the contents of another cell if the the sister column of the range includes the exact variant?

Question

I have a sheet with the following cell contents:

Sheet1

1. A2: "how to choose between right and wrong"
2. A3: "what is the best baseball bat"

What I am trying to do is come up with a formula that reviews the range of another sheet (Sheet2!A2:B) and provides the first match result in column B of the same row (see screenshot).

Example: List item 1 (above) should show up for "Prepositional" and List item 2 (above) should show up for "Informational"

However, because the word bat has "at" in it the Intent column it chooses is "Prepositional"

Here is the formula I have thus far

=INDEX(Sheet2!$B$1:$B,MATCH(TRUE,ISNUMBER(SEARCH(Sheet2!$A$1:$A,A2)),0))

How do you retrieve the contents of another cell if the the sister column of the range includes the exact variant?

Answer 1

try:

=ARRAYFORMULA(IFNA(VLOOKUP(REGEXEXTRACT(A2:A, "^\w+"), D:E, 2, 0)))

or if you are not looking always for the first word:

=ARRAYFORMULA(IFNA(VLOOKUP(REGEXEXTRACT(A2:A, 
 TEXTJOIN("|", 1, D:D)), D:E, 2, 0)))

---

update:

=ARRAYFORMULA(IFNA(VLOOKUP(REGEXEXTRACT(A2:A, 
 "\b"&TEXTJOIN("\b|\b", 1, Intent!A2:A)&"\b"), Intent!A2:B, 2, 0)))