Find

qcd

New Member
Book1
ABCDEFGHIJK
1
2ACE
3BDF
4
5ABCDE110
6FGHIJ
7
8BCDEF011
9GHIJK
10
11CDEFG011
12HIJKL
13
14ZYXWV000
15UTSRQ
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?

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},\$Z\$1:\$Z\$99,\$Y\$1:\$Y\$99),2,False) to lookup Y values to left of Z values.

qcd

New Member
Got it !

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

qcd

New Member
dddddddd.xls
ABCDEFGHIJK
1
2ACE
3BDF
4
5ABCDE124
6FGHIJ
7
8BCDEF
9GHIJK
10
11CBEFG
12HIJKL
13
14ZYEFV
15UTSRQ
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

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

Zack Barresse

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

qcd

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

Zack Barresse

MrExcel MVP
something like this??...
WTR03.xls
ABCDEFGHIJ
1
2ACE
3BDF
4
5ABCDE124
6FGHIJ
7
8BCDEF
9GHIJK
10
11CBEFG
12HIJKL
13
14ZYEFV
15UTSRQ
Sheet2

qcd

New Member
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.

Any thoughts !!!

Replies
3
Views
276
Replies
1
Views
632
Replies
10
Views
187
Replies
3
Views
73
Replies
3
Views
135

1,186,156
Messages
5,956,254
Members
438,243
Latest member
FXA

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.

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

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