Formula for matching numbers

Kalpesh64

New Member
Joined
Apr 17, 2020
Messages
4
Office Version
  1. 2016
Platform
  1. Windows
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
    Formula.png
    57.4 KB · Views: 10

Excel Facts

Links? Where??
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
Joined
Dec 9, 2003
Messages
1,133
Office Version
  1. 2019
Platform
  1. Windows
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))
 
Solution

Kalpesh64

New Member
Joined
Apr 17, 2020
Messages
4
Office Version
  1. 2016
Platform
  1. Windows
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
Joined
Dec 9, 2003
Messages
1,133
Office Version
  1. 2019
Platform
  1. Windows
That's great. I'm happy to help.
 

Watch MrExcel Video

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top