Formula for matching numbers

Kalpesh64

New Member
I have a table with 8 columns and 1000 rows. I would to enter a formula in column M, which will check each rows (A1:H1000) for 2 numbers from J1 and K1. If a row has both of this number it will enter "1" in the cell in column M and if not, it will enter "0".

Attachments

• Formula.png
57.4 KB · Views: 10

Excel Facts

If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

KRice

Well-known Member
Here is one idea:
Book2
ABCDEFGHIJKLM
1111226345611261
21111142611781
312342661181
41226456780
Sheet2
Cell Formulas
RangeFormula
M1:M4M1=--AND(COUNTIF(A1:H1,\$J\$1),COUNTIF(A1:H1,\$K\$1))

Kalpesh64

New Member
Here is one idea:
Book2
ABCDEFGHIJKLM
1111226345611261
21111142611781
312342661181
41226456780
Sheet2
Cell Formulas
RangeFormula
M1:M4M1=--AND(COUNTIF(A1:H1,\$J\$1),COUNTIF(A1:H1,\$K\$1))
Thank you KRice. It's perfect.

KRice

Well-known Member
That's great. I'm happy to help.

Replies
1
Views
34
Replies
10
Views
193
Replies
9
Views
173
Replies
10
Views
182
Replies
5
Views
278

1,130,015
Messages
5,639,558
Members
417,098
Latest member
steverob

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