If Match formula

[TelecomMI]

I am looking to have the function look for multiple terms inside of cell to return a "X" as a check if there is a match. The formula only works on the first part of the formula. So I tried working in AND. It then only gives an NA

=IF(AND(MATCH("*City of Grand Rapids*",R6,0),IF(MATCH("*City of Grandville*",R6,0),"X")))

There is 3 more pieces of criteria to add in the search, but I cannot seem to get the answer from the first two in the function.

 

[Aladin Akyurek]

Must both City of Grand Rapids and City of Grandville occur as part of the string in R6?

 

[TelecomMI]

Those key words are the reason for the formula. I need it to find these to verify them on the report.

 

[FDibbins]

Try this...

	A​	B​
1​	aabbccddee	x
2​	aabbffddee

B1=IF(ISERROR(AND(FIND("bb",A1),FIND("cc",A1))),"","x")
copied down

 

[FDibbins]

Quick question - will those words always be in that order?

If so, then...
=IF(ISERROR(FIND("bb"&"cc",A1)),"","x")

 

[Aladin Akyurek]

Those key words are the reason for the formula. I need it to find these to verify them on the report.

Must they occur at the same time in R6 in order to return/conclude X?

 

[TelecomMI]

I should of stated that. The formula will be copied down through column p with the correlating number in r. The city will only be one city occurring in column r. Ex r6 city of Grand Rapids, r23 city of Grandville, and so on.

 

[FDibbins]

I think you need to provide a range of sample data, and show what you expect.

Did you try my suggestion from post #4?
adjusted to your last post...
P1=IF(ISERROR(AND(FIND("Grand Rapids",R1),FIND("Grandville",R1))),"","x")

 

[TelecomMI]

This formula seems to return X for everything and not whether there is a matching portion of the formula.

 

[Aladin Akyurek]

I should of stated that. The formula will be copied down through column p with the correlating number in r. The city will only be one city occurring in column r. Ex r6 city of Grand Rapids, r23 city of Grandville, and so on.

Care to post what you have in R6?