Find and Match Multiple Text

[kumara_faith]

Hi,

I have a list of notes keyed in by users in a system. I need to find if there is a match for the word "payment", "agent" and "approve". If all three words exist, then to return as True or else False. I tried the formula =SUMPRODUCT(--ISNUMBER(SEARCH(things,A1)))>0, but this seems to only return if any one of the values is in the cell. An example of correct result is as below:

Book3
	B	C
3	Notes	Result
4	Agent made payment on behalf of customer, approved	TRUE
5	Online payment fail, agent payment approved	FALSE
Sheet1

Appreciate all the help.

 

[jasonb75]

Try changing >0 to =3

Also, your example is wrong, both are TRUE as per the specified criteria.

 

[BigBeachBananas]

Hi,

I have a list of notes keyed in by users in a system. I need to find if there is a match for the word "payment", "agent" and "approve". If all three words exist, then to return as True or else False. I tried the formula =SUMPRODUCT(--ISNUMBER(SEARCH(things,A1)))>0, but this seems to only return if any one of the values is in the cell. An example of correct result is as below:

Book3
	B	C
3	Notes	Result
4	Agent made payment on behalf of customer, approved	TRUE
5	Online payment fail, agent payment approved	FALSE
Sheet1

Appreciate all the help.

Not understanding your desired output in row 5. All three words "agent", "approve", and "payment" exist, why is it FALSE?

 

[BigBeachBananas]

One more thing to consider is that it is possible for you to upper and lower case, so it's necessary to force your text in either lower or upper case.
Try this:

Book2
	A	B	C	D	E
1	Notes	AGENT	PAYMENT	APPROVE	ALL 3
2	Agent made payment on behalf of customer, approved	TRUE	TRUE	TRUE	TRUE
3	Online payment fail, agent payment approved	TRUE	TRUE	TRUE	TRUE
Sheet1

Cell Formulas
Range	Formula
B2:B3	B2	=IF(ISNA(FIND("agent",LOWER($A2)))=FALSE,TRUE,FALSE)
C2:C3	C2	=IF(ISNA(FIND("payment",LOWER($A2)))=FALSE,TRUE,FALSE)
D2:D3	D2	=IF(ISNA(FIND("approve",LOWER($A2)))=FALSE,TRUE,FALSE)
E2:E3	E2	=IF(AND(B2=TRUE,C2=TRUE,D2=TRUE),TRUE,FALSE)

 

[AlanY]

try this

Book1
	A	B	C	D
1	Agent made payment on behalf of customer, approved	TRUE		payment
2	Online payment fail, agent payment	FALSE		agent
3	payment	FALSE		approve
98

Cell Formulas
Range	Formula
B1:B3	B1	=IFERROR(IF(SUM(1/ISNUMBER(SEARCH($D$1:$D$3,A1)))=3,TRUE),FALSE)

 

[jasonb75]

so it's necessary to force your text in either lower or upper case.

It is not necessary, the SEARCH function that they are already using ignores case. As I pointed out in post 2, it just needs a very simple change to the existing formula, everyone else just seems to be obsessed with cluttering the thread with pointless replies.

 

[BigBeachBananas]

It is not necessary, the SEARCH function that they are already using ignores case. As I pointed out in post 2, it just needs a very simple change to the existing formula, everyone else just seems to be obsessed with cluttering the thread with pointless replies.

With due respect, it's an open forum where people can offer different solutions to a problem. Admitted I did mix up between FIND and Search function where letter-casing matters. However, calling people's relies "pointless" is ignorant. What makes your solution the best solution? There might be someone who can offer a better solution than yours. We're here trying to help, not to make things more complicated. Please reframe from using such language in the future.

 

[jasonb75]

The 'pointless' comment was mainly directed at your first reply which, although phrased differently, did nothing more than repeat what I had already said in post 2. Your follow up was 5 minutes later so you could have easily edited your previous reply instead of adding more clutter to the thread.

I'm not saying that my solution is the best solution, but at least it was a solution where as yours was merely an answer that gets there eventually.

Final point, although my response was far from diplomatic, there was nothing wrong with the language, and, as you are not a member of the site moderation or admin team, you have no authority to make any kind of demand in regard to my conduct. Thanks for your input.

 

[BigBeachBananas]

The 'pointless' comment was mainly directed at your first reply which, although phrased differently, did nothing more than repeat what I had already said in post 2. Your follow up was 5 minutes later so you could have easily edited your previous reply instead of adding more clutter to the thread.

I'm not saying that my solution is the best solution, but at least it was a solution where as yours was merely an answer that gets there eventually.

Final point, although my response was far from diplomatic, there was nothing wrong with the language, and, as you are not a member of the site moderation or admin team, you have no authority to make any kind of demand in regard to my conduct. Thanks for your input.

To your first point, your remark of "pointless cluttering" was a direct reply to my solution post.

To your second point, when I raise the point: "What makes your solution the best solution? There might be someone who can offer a better solution than yours." I was making a general statement, not necessarily specific to this thread.

To your final point, I was not making a "demand of any kind of demand in regard to [your] conduct." I was merely suggesting to improve the forum. If a member of the admin team needs to be involved in your conduct, then so be it.

Lastly, I will no longer be replying to this thread as I'm "obsessed with cluttering the thread with pointless replies." and this conversation deviates from the original purpose of the OP.

 

[jasonb75]

To your first point, your remark of "pointless cluttering" was a direct reply to my solution post.

No, the first sentence highlighting the fact that it was not necessary to force case was a direct reply to your suggested answer (not solution, there is a difference).