Find

[qcd]

Book1
	A	B	C	D	E	F	G	H	I	J	K
1
2								A	C	E
3								B	D	F
4
5		A	B	C	D	E		1	1	0
6		F	G	H	I	J
7
8		B	C	D	E	F		0	1	1
9		G	H	I	J	K
10
11		C	D	E	F	G		0	1	1
12		H	I	J	K	L
13
14		Z	Y	X	W	V		0	0	0
15		U	T	S	R	Q
16
Sheet1

I need formulas in the shaded cells.

Cell H2 contains "A" and H3 contains "B". The formula in H5 should look for "A" and "B" both in the range B5:F6. If found, show "1" else show "0".

The same thing should happen with all the shaded cells.

Can this be done?

 

[qcd]

Got it !

The formula in H5 should be =IF(COUNTIF(B5:F6,$H$2)+COUNTIF(B5:F6,$H$3)=2,1,0)

 

[qcd]

dddddddd.xls
	A	B	C	D	E	F	G	H	I	J	K
1
2								A	C	E
3								B	D	F
4
5		A	B	C	D	E		1	2	4
6		F	G	H	I	J
7
8		B	C	D	E	F
9		G	H	I	J	K
10
11		C	B	E	F	G
12		H	I	J	K	L
13
14		Z	Y	E	F	V
15		U	T	S	R	Q
16
Sheet1

A little tough one this time:

Cell H5 will look for "A" and "B" in all the ranges that is, B5:F6, B8:F9, B11:F12 & B14:F15 and will tell the count how many times it appeared together. In this case only once.

Similarly, Cell I5 will look for "C" and "D" in all the ranges that is, B5:F6, B8:F9, B11:F12 & B14:F15 and will tell the count how many times it appeared together. In this case only twice.

Similarly, Cell J5 will look for "E" and "F" in all the ranges that is, B5:F6, B8:F9, B11:F12 & B14:F15 and will tell the count how many times it appeared together. In this case four times.

I am finding very tough to perform this task. Any help will be appreciated.

 

[qcd]

Somebody please help me. I am not getting the desired results. I have tried a lot of things to no avail.

 

[Zack Barresse]

what dictates what goes into range of B5:F15??

 

[qcd]

I didn't get you Zack. Those are just random alphabets. Thats it.

 

[Zack Barresse]

something like this??...

WTR03.xls
	A	B	C	D	E	F	G	H	I	J
1
2								A	C	E
3								B	D	F
4
5		A	B	C	D	E		1	2	4
6		F	G	H	I	J
7
8		B	C	D	E	F
9		G	H	I	J	K
10
11		C	B	E	F	G
12		H	I	J	K	L
13
14		Z	Y	E	F	V
15		U	T	S	R	Q
Sheet2

 

[qcd]

Exactly Zack but there is a problem. The data that I have posted is just a sample data, I have 500 ranges like those, if I use your logic, the formula will be as big as an essay.

 

[qcd]

Any thoughts !!!