Does this cell contain data in this vertical array?

matt_ward

New Member
Joined
Jul 6, 2015
Messages
19
So i have a list of values in an array (Color) and cells that may contain those values that are separated by a comer (DATA). I am looking for an equation that can give me either TRUE or FALSE or a Count of instances. Feel a Search and Countifs in there somewhere?

ColorAnswerData
BlueTRUE or 2RED,BLUE,GREEN,ORANGE
PurpleFALSEIndigo,Violet
BlackTRUE or 1RED,VIOLET,GREEN
White
Green
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
If I understand you correctly, here is a formula that will tell you if the value in cell A2 is found in cell C2:
Excel Formula:
=ISNUMBER(FIND(UPPER(A2),UPPER(C2)))
Note: I think you may have an error on your third line. "Black" is not found anywhere in the "Data" cell, so not sure why you would want that to return "True".
 
Upvote 0
Hi, here is another take on it that you can try.

Book3
ABC
1ColorAnswerData
2Blue2RED,BLUE,GREEN,ORANGE
3Purple0Indigo,Violet
4Black1RED,VIOLET,GREEN
5White
6Green
Sheet1
Cell Formulas
RangeFormula
B2:B4B2=SUMPRODUCT(0+ISNUMBER(SEARCH(","&$A$2:$A$6&",",","&C2&",")))
 
Upvote 0
Solution
If I understand you correctly, here is a formula that will tell you if the value in cell A2 is found in cell C2:
Excel Formula:
=ISNUMBER(FIND(UPPER(A2),UPPER(C2)))
Note: I think you may have an error on your third line. "Black" is not found anywhere in the "Data" cell, so not sure why you would want that to return "True".
The list of Colors in A is the Array. (Just data for reference.. the color might not be listed)

I am trying to find a calculation that tells me is anything from A in anything in C with the answer going into B. (the answer could be True or False)
 
Upvote 0
The list of Colors in A is the Array. (Just data for reference.. the color might not be listed)

I am trying to find a calculation that tells me is anything from A in anything in C with the answer going into B. (the answer could be True or False)
I am just saying that your example is incorrect, based on the data you posted.

Did you try my formula?
That is exactly what it does.
 
Upvote 0
Let me put together a better example.

A (Reference Range)B (the calculation is a count of how many of those in the reference range appear in the Cell in the Data Range)C (Data)
JohnJohn,James,Brian
BrianSteve,Daniel,Brian,James,Oliver,Catherine,Sally
Steve
Daniel
James
 
Upvote 0
Let me put together a better example.

A (Reference Range)B (the calculation is a count of how many of those in the reference range appear in the Cell in the Data Range)C (Data)
JohnJohn,James,Brian
BrianSteve,Daniel,Brian,James,Oliver,Catherine,Sally
Steve
Daniel
James
In the above for the first line it would be 3, for the second line it would be 4. The length of the data I have in C could include up to 100 names.
 
Upvote 0
OK, I think I misunderstood your question, and was searching the other way, taking the values in column A and searching for them in column C, instead of vice versa.
If that is the case, I think FormR's solution does what you want. Did you try it?
 
Upvote 0
OK, I think I misunderstood your question, and was searching the other way, taking the values in column A and searching for them in column C, instead of vice versa.
If that is the case, I think FormR's solution does what you want. Did you try it?
Yes FromR got this one... I need to bone up more on what encasing & in calculations does. Thanks for your contribution though. and thanks FormR for the solution.
 
Upvote 0
Also to add... always better to define the range than to select the Column that the range is in. SO Much quicker to calculate.
 
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,938
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top