Find a text in a sentence column

[XcelProMax]

Hi,
I have col A and col B as text fields. In col C I have a query list. I'm trying to find if the text values in both col A and col B exist anywhere in col C and then return a true or a false. Please see the attached sheet.

Thanks.

[FlameRetired]

Hello XcelProMax. Welcome to the forum.

I will need more explanation.

I do not find that Query in row 2 contains either FACT_TABLE or CORE_TABLE values in row 2. How do you get True in Output D2?

[PeteABC123]

From the description provided, the values in A2:B2 are found in the contents of C4. XcelProMax indicated that the trigger for true can be found anywhere in Col. C. C4 meets the requirement, so D2 is correctly labelled TRUE.

Still trying to figure out a solution. BB soon.


Pete

[FlameRetired]

Ah yes. It seems i struggle understanding plain English statements.

[FlameRetired]

Try this in D2 and fill down.

Please Login or Register  to view this content.

Edit: This is better.

Please Login or Register  to view this content.

[GeoffW283]

Dave - I initially read it the same way as you. And just a note for XcelProMax: your formula is an array formula needing CTRL-SHIFT-ENTER with XcelProMax's Excel version.

Also - I think your formula gives TRUE if just one of the conditions is TRUE. Don't they both need to be TRUE??

A different try:

Please Login or Register  to view this content.

[AliGW]

Administrative Note:

Welcome to the forum.

We would very much like to help you with your query, however it has been brought to our attention that the same query has been posted on one or more other forums and you have not provided the required cross-post link(s) here.

Please see Forum Rule #3 about cross-posting and adjust accordingly. Read this to understand why we (and other sites like us) consider this to be important.

(Note: this requirement is not optional. As you are new here, I will provide the link for you this time: https://www.mrexcel.com/board/thread...olumn.1183493/.)

[menem]

Please try at D2

=OR(COUNTIF($C$2:$C$4,"*"&A2&"*"),COUNTIF($C$2:$C$4,"*"&B2&"*"))

Regards.

[kvsrinivasamurthy]

Try this in F2 then copied down

=SUMPRODUCT(1*(ISNUMBER(SEARCH(A2:B2,$C$2:$C$10))))=2

[GeoffW283]

Not surprisingly the five offered formulas each give the expected results per XcelProMax's one test case.

I created 3 additional test cases and found that none of the formulas (including my own) match my (possibly erroneous) understanding of the expected outcome. Here's a summary:

query.png

The attached workbook is an update to XcelProMax's attachment.

• I think (hope) I have transcribed the various formulas into Main!D2:I4 correctly.
• The 4 test cases I ran are on the "Cases" worksheet. The changes I made to col-C for each case are highlighted in red.
• Cases!D1:J19 (the image above) is a snapshot of the formula results for each case obtained by copying each case in turn from the "Cases" worksheet and pasting to Main!A2:D4

Hopefully I have done this accurately, let me know if not. Maybe XcelProMax can say that some or all of my extra test cases can never occur in practice and thus be able to choose one or more of the already offered formulas.

I hope this helps rather than confuses!

[kvsrinivasamurthy]

modified formula

=SUMPRODUCT(1*(ISNUMBER(SEARCH(A3:B3,$C$2:$C$4))))>=2

[menem]

Maybe I misunderstood OP's request, so I use OR instead AND

modify formula from
=OR(COUNTIF($C$2:$C$4,"*"&A2&"*"),COUNTIF($C$2:$C$4,"*"&B2&"*"))
to
=AND(COUNTIF($C$2:$C$4,"*"&A2&"*"),COUNTIF($C$2:$C$4,"*"&B2&"*"))

Regards.