Excel: Find out if cell contains/includes value fr

问题:

I have a list of job titles (A) and list of phrases (B). For each title in A I want to check if it contains a phrase (any phrase, I don't care which) from B.

| 1 |-----Example Column A------|----Example Column B----        
| 2 |    Head of Marketing      |    Senior Developer          
| 3 |  Lead Product Engineer    |    Marketing Manager       
| 4 |     Sales Development     |    Sales Development   
| 5 |  Senior Marketing Manager |      

In the above example, I want to know that the last two cells in column A contain cells in column B.

I found lots of examples online of how to do the reverse, using * to find if a value is contained in a range. I also found the following three examples offered as solutions for problems similar to mine, but none worked for me.

{=MAX(ISNUMBER(SEARCH($B$2:$B$4,A2))+0)}         
{=MATCH(A2,$B$2:$B$4&"*")}
=IFERROR(LOOKUP(2^15,SEARCH(B:B,A2),B:B),"")

I've also tried writing the contents of each cell in column B to start and end with an asterisk (*Senior Developer *, etc.), trying VLOOKUP, SUMPRODUCT, COUNTIF without success.

Is it possible to do what I want?

Solution

Slightly modified Mrig's formula:

=SUMPRODUCT(ISNUMBER(FIND(B$2:B$4,LOWER(A2)))*1)

回答1:

Try this

=SUMPRODUCT(ISNUMBER(FIND($B$2:$B$4,$A2))*1)

This formula will return the number of phrases matched.

You can put this formula in IF if you don't want the count.

=IF(SUMPRODUCT(ISNUMBER(FIND($B$2:$B$4,$A2))*1)>0,"Exist","Does Not Exist")

See image for reference

EDIT: After sorting Column A in descending order output I get is:

回答2:

Put the following formula in col C for each row in col A (starting at C2)

=IF(ISNA(INDEX(B$2:B$4,MATCH(A2,B$2:B$4,0))),"","x")

This is a combination of the INDEX and MATCH functions. INDEX returns a cell within a range based on a count, which in this case is provided by the MATCH function which returns the position of a cell within an array by matching against a criteria string.

The ISNA function is there to hide the "N/A" output in any non-matching cells and display "x" against any matching cells.

Have a look here for a more in-depth explanation.

UPDATE

As per the comment below, in order to see if Col A contains the text in Col B (rather than being a direct match) then use this formula in col C for each row:

=IF(SUMPRODUCT(COUNTIF(A2,"*" & B$2:B$4 & "*"))>0, "X", "")