If both A1 and B1 exist within any single cell within Column C (array) *help*

S

shaun@ts

New member
Joined
Nov 22, 2016
Messages
4
Reaction score
0
Points
0
Hi All

Seems like a simple request, however it certainly hasn't been!

If both A1 and B1 exist within any single cell within Column C data array, then D1="YES"

Appreciate any help with this, thanks.
 
Last edited:
Hi,

not sure to have understood

=IF(COUNTIF(C2:C100,"*"&A1&"*")+COUNTIF(C2:C100,"*"&B1&"*")=COUNTA(C2:C100)*2,"YES","")

Maybe I need a small example
 
Hi,


in D2

=IF(COUNTIF($C$2:$C$18,A2&" *"&B2&"*"),"YES","")

there is a "space" after A2&"space...in order to better isolate and distinguish - for example - Text1 and Text10.


Row 10 has no match...

Regards
 
Last edited:
Appreciate your help, however the results on my side return a blank.

Would you kindly attach the s/s you have worked with, and I'll have a look there.

Thanks.
 
Hi,

please refer to the attachment.
 

Attachments

  • Sample (it).xlsx
    18.2 KB · Views: 7
Hi,


Maybe in your actual database formulas could need an extra "*" to work properly.


=IF(COUNTIF($C$2:$C$18,"*"&A2&" *"&B2&"*"),"YES","")

or you could need to check if the two substrings appear in different order:

=IF(COUNTIF($C$2:$C$18,"*"&A2&" *"&B2&"*")+COUNTIF($C$2:$C$18,"*"&B2&" *"&A2&"*"),"YES","")


More or less same formulas.

I hope it's of some help.


Regards
 
Good morning,

That works just perfectly - thank you for all your help, I really appreciate it!
Fantastic skills :clap2:
 
You must log in or register to reply here.
Back
Top