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.
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.
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?
Dave
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
Ah yes. It seems i struggle understanding plain English statements.
Try this in D2 and fill down.Formula:Please Login or Register to view this content.
Edit: This is better.Formula:Please Login or Register to view this content.
Last edited by FlameRetired; 10-01-2021 at 03:29 PM.
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:
Formula:Please Login or Register to view this content.
Last edited by GeoffW283; 10-01-2021 at 04:28 PM.
Geoff
Did I help significantly? If you wish, click on * Add Reputation to say thanks.
If your problem has been resolved please select ?Solved? from the Thread Tools menu
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/.)
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
Please try at D2
=OR(COUNTIF($C$2:$C$4,"*"&A2&"*"),COUNTIF($C$2:$C$4,"*"&B2&"*"))
Regards.
My English is very poor, so please be patient >_<"
My Dynamic Related Dropdown post:
https://www.excelforum.com/excel-for...ml#post5657376
https://www.excelforum.com/excel-for...ml#post5655711
https://www.excelforum.com/excel-for...ml#post5655762
Try this in F2 then copied down
=SUMPRODUCT(1*(ISNUMBER(SEARCH(A2:B2,$C$2:$C$10))))=2
Pl note
Array formula should be confirmed with Ctrl+Shift+Enter keys together.
If answere is satisfactory press * to add reputation.
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!
modified formula
=SUMPRODUCT(1*(ISNUMBER(SEARCH(A3:B3,$C$2:$C$4))))>=2
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.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks