Formula to evaluate if the a data range contains every value passed

[TristanB]

Hi, I'm trying to come up with a formula that takes:

a list of values cells

a list of data cells

and checks if all the values are present in at least one of the data cell.


let's say the formula receives:

Values: 1,2,3

Data: 3,3,2,1,2,5

It would return TRUE


Let's say the formula receives:

Values: "Mom", "Dad", "Dad"

Data: "Mom", "Dad"

It would return TRUE


LEt's say the formula receives:

Value: 1

Data: 2,3,4,5,11,10,21

It would return FALSE

 

[Hui]

TristanB


Firstly, Welcome to the Chandoo.org forums.


You may want to have a look at:

http://chandoo.org/wp/2012/02/09/formula-forensics-011/

or

http://chandoo.org/wp/2012/02/01/formula-forensics-no-010/

 

[NARAYANK991]

Hi Tristan ,


Let us assume you have your worksheet as follows :


Your data consisting of numbers / text is named data_cells


Your values to be checked against the above data is named values_cells


Then the following formula , entered as an array formula , using CTRL SHIFT ENTER , will return a TRUE / FALSE value , depending on whether the values exist anywhere in your data cells :


=OR(TRANSPOSE(Data_cells)=Values_cells)


Courtesy : http://www.emailoffice.com/excel/arrays-bobumlas.html


The above link also explains the limitations of this formula , and a work-around.


Narayan

 

[TristanB]

Thank you very much to both of you