Search for two values appearing in a cell at the same time

[jdunbarjr]

I am trying to search two columns of values (column I and J) where both appear at the same time in the same string/cell in column H. Let' say value (I11) and (J11) appear at the same time in column H cell (H11), and if yes/true, provides the H11 value in column W cell (W11). I have tried several formulas and cannot figure it out. For example:

=IF(OR(ISNUMBER(SEARCH("*I11*",`CBS SHEET1'!F:F)),ISNUMBER(SEARCH("*J11*",`CBS SHEET1'!F:F))),"1","-")

.

 

[Special-K99]

"where both appear at the same time"
Then why are you using OR? It should be AND.

"in column H"
Then why are you searching column F ?

Will I11 and J11 only occur once in column H ?

 

[jdunbarjr]

Yeah, I am sure that I am doing it wrong. I am trying to take the values from let's say cell I11 and J11 and see if they both appear in the same string/cell anywhere in column F, and if yes, then I would like that value from column F (that has both I11 and J11 values) to show up in column W. For example: if cell I11 = "cat", cell J11 = "dog", and some cell (perhaps F20) in column F = "cat.dog", then I would like in cell W11 to show "cat.dog". I hope this makes more sense.

 

[jtakw]

Hi,

What if F is "dog.cat", is that considered a match?
Also, is "cat.dog.other" a match?

And are the values in F all delimited by a period ( . ) or something else?

 

[jdunbarjr]

Yes, as long as both I11 and J11 are within the same cell anywhere in column F .. it would be considered a match, even if there is other data within that cell too.

 

[jtakw]

Then this should do what you described:

Book1
	F	G	H	I	J	W
11	cat.mouse			cat	dog	some dog and some cat
12
13	dog bone
14
15	cat litter
16
17	food dog
18
19
20	some dog and some cat
21	food cat
Sheet539

Cell Formulas
Range	Formula
W11	=IFERROR(IFERROR(LOOKUP(2,1/SEARCH(I11&"*"&J11,F11:F21),F11:F21),LOOKUP(2,1/SEARCH(J11&"*"&I11,F11:F21),F11:F21)),"No Match")