# Can a formula do this?

#### weegieavlover

##### Board Regular
I need a formula that is in a cell to be able to insert a 1 or 0 in another cell - is that possible:

What I need is a formula that does this:

if column a1:a10 = x
AND
if column b1:b10 = y
THEN
insert "0" in column c1:c10 (where the match is)
IF NOT
insert "1" in column c1:c10 (where there is no match)

Hopefully that makes sense.

Regards
Colin

### Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

##### MrExcel MVP
I need a formula that is in a cell to be able to insert a 1 or 0 in another cell - is that possible:

What I need is a formula that does this:

if column a1:a10 = x
AND
if column b1:b10 = y
THEN
insert "0" in column c1:c10 (where the match is)
IF NOT
insert "1" in column c1:c10 (where there is no match)

Hopefully that makes sense.

Regards
Colin
C1, just enter & copy down:

=AND(A1="x",B1="y")+0

#### weegieavlover

##### Board Regular
C1, just enter & copy down:

=AND(A1="x",B1="y")+0

I am liking that and never realised it was so easy however I need A1 & B1 in your formula to be a range - I have tried altering it:

=AND(A1:A10=G1:G10,B1:B10=H1:H10)+0

however it is returning the value of 0 which is incorrect.

Thanks again for the assist.

##### MrExcel MVP

I am liking that and never realised it was so easy however I need A1 & B1 in your formula to be a range - I have tried altering it:

=AND(A1:A10=G1:G10,B1:B10=H1:H10)+0

however it is returning the value of 0 which is incorrect.

Thanks again for the assist.

Control+shift+enter, not just enter, and copy down:

=ISNUMBER(MATCH(1,IF(\$A\$1:\$A\$10=G1,IF(\$B\$1:\$B\$10=H1,1)),0))+0

Is this what you are after?

#### weegieavlover

##### Board Regular

Control+shift+enter, not just enter, and copy down:

=ISNUMBER(MATCH(1,IF(\$A\$1:\$A\$10=G1,IF(\$B\$1:\$B\$10=H1,1)),0))+0

Is this what you are after?

I think it is getting there.
However does "ISNUMBER" mean that is has to be numbers it is looking for in G1 & H1?

If it is looking for a number then this maybe the problem.
G1 will = a server name
H1 will = a HEX number.

=ISNUMBER(MATCH(1,IF(\$A\$1:\$A\$10=G1,IF(\$B\$1:\$B\$10=H1,1)),0))+0

Both fields will be in "text" format though.

I am still getting zero when I should be getting some 1's.
I have put the table below so you can visually see what I am trying to do (as an example)

<TABLE style="WIDTH: 593pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=790 border=0 x:str><COLGROUP><COL style="WIDTH: 82pt; mso-width-source: userset; mso-width-alt: 3986" width=109><COL style="WIDTH: 28pt; mso-width-source: userset; mso-width-alt: 1353" width=37><COL style="WIDTH: 12pt; mso-width-source: userset; mso-width-alt: 585" span=3 width=16><COL style="WIDTH: 11pt; mso-width-source: userset; mso-width-alt: 548" width=15><COL style="WIDTH: 41pt; mso-width-source: userset; mso-width-alt: 2011" width=55><COL style="WIDTH: 28pt; mso-width-source: userset; mso-width-alt: 1353" width=37><COL style="WIDTH: 367pt; mso-width-source: userset; mso-width-alt: 17883" width=489><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 id=td_post_2665265 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 82pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=109 height=17>A</TD><TD class=xl24 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 28pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=37>B</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 12pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=16>C</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 12pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=16>D</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 12pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=16>E</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 11pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=15>F</TD><TD class=xl24 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 41pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=55>G</TD><TD class=xl24 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 28pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=37>H</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 367pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=489>I</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>HOMER</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">0E71</TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">HOMER</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">0E71</TD><TD class=xl27 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">=ISNUMBER(MATCH(1,IF(\$A\$1:\$A\$10=G1,IF(\$B\$1:\$B\$10=H1,1)),0))+0</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>BART</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">1489</TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">BART</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">1489</TD><TD class=xl27 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">=ISNUMBER(MATCH(1,IF(\$A\$1:\$A\$10=G1,IF(\$B\$1:\$B\$10=H1,1)),0))+0</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl28 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>HOMER</TD><TD class=xl28 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">1489</TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl27 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl28 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>LISA</TD><TD class=xl28 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">1AB2</TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl27 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD></TR></TBODY></TABLE>
Formula in cell I.
Looks at column A1:A10 for any matches in G1.
Looks at column B1:B10 for any matches in H1
If there is a match then update the same row in column C

It should look at column A & Column B and if there are any matches with what is in G1 or H1 then it updates the equivelant cell in column C.

Apologies if my waffling is making no sense.

Col

Any suggestions?

#### energman58

##### Well-known Member

In Aladins formula ISNUMBER just returns a True or False depending on whether the argument is a number or not. SO it looks to see if there is a match for both your G1 and H1 values if it is then MATCH will return a number (depending on the place it appears in the array) if not it returns #N/A (I think - anyway a non number answer) and based on that you get either a TRUE or FALSE. True is equivalent to 1 and False is equivalent to 0 in mathematical operations so something like

=ISNUMBER(MATCH(G1,A1:A10,0))*ISNUMBER(MATCH(H1,B1:B10,0))

Would return 1 or 0 correctly (True*True = 1*1 =1 False*True or False *False = 0)

However, I think part of your problem may be that some of the HEX values in B are evaluating as numbers and others as text so maybe if you format them all as Text it may work better as excel does wierd stuff with words that look like numbers - as long as everything is formatted as text it should be OK.

Also you may need to make sure there are no leading or trailing spaces which might affect the comparisons.

Hope the weatehr in the 'Weege is as good as ever - 28C here today (sorry to gloat)

HTH

Last edited:

#### John MCarthy

##### New Member
Counting the # of rows with a value in an excel range

<HR style="COLOR: #ffffff; BACKGROUND-COLOR: #ffffff" SIZE=1><!-- / icon and title --><!-- message -->Hi guys,

Probably a bit of a basic question for experienced campaigners like you guys but have wasted enough time on MS Help on this one so am hoping you guys might be able to help - here goes:

I am working with a table of data in excel. Under each column I want to count the number of rows to the left of the column that have at least one cell with a number >0 (greater than 0) in the row.

I have been trying variations of COUNT and COUNTIF and am not having any success - can anyone pls provide a formula for this?

Thanks,

John

#### weegieavlover

##### Board Regular
=ISNUMBER(MATCH(G1,A1:A10,0))*ISNUMBER(MATCH(H1,B1:B10,0))

Would return 1 or 0 correctly (True*True = 1*1 =1 False*True or False *False = 0)

Hope the weatehr in the 'Weege is as good as ever - 28C here today (sorry to gloat)

HTH

Understood - your logic makes sense but the formula is acting strangely.

And that is working a treat - however I need it to do one more thing.

the formula is going to be in Column I (hightlighted in blue) and I need the 1 or 0 to place in Column C on the correct row.

<TABLE style="WIDTH: 372pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=496 border=0 x:str><COLGROUP><COL style="WIDTH: 82pt; mso-width-source: userset; mso-width-alt: 3986" width=109><COL style="WIDTH: 29pt; mso-width-source: userset; mso-width-alt: 1426" width=39><COL style="WIDTH: 30pt; mso-width-source: userset; mso-width-alt: 1462" width=40><COL style="WIDTH: 30pt; mso-width-source: userset; mso-width-alt: 1462" width=40><COL style="WIDTH: 30pt; mso-width-source: userset; mso-width-alt: 1462" span=2 width=40><COL style="WIDTH: 82pt; mso-width-source: userset; mso-width-alt: 3986" width=109><COL style="WIDTH: 29pt; mso-width-source: userset; mso-width-alt: 1426" width=39><COL style="WIDTH: 30pt; mso-width-source: userset; mso-width-alt: 1462" width=40><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl28 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 82pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=109 height=17>A</TD><TD class=xl28 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 29pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=39>B</TD><TD class=xl28 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 30pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=40>C</TD><TD class=xl29 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 30pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=40>D</TD><TD class=xl24 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 30pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=40>E</TD><TD class=xl24 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 30pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=40>F</TD><TD class=xl28 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 82pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=109>G</TD><TD class=xl28 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 29pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=39>H</TD><TD class=xl24 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 30pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=40>I</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl30 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>HOMER</TD><TD class=xl30 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">0E71</TD><TD class=xl31 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: red"> </TD><TD class=xl27 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl30 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">HOMER</TD><TD class=xl30 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">0E71</TD><TD class=xl32 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: blue"> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl30 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>BART</TD><TD class=xl30 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">1489</TD><TD class=xl31 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: red"> </TD><TD class=xl27 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl30 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">BART</TD><TD class=xl30 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">1489</TD><TD class=xl33 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: blue"> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>HOMER</TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">1489</TD><TD class=xl31 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: red"> </TD><TD class=xl27 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl30 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl30 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl33 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: blue"> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>LISA</TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">1AB2</TD><TD class=xl31 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: red"> </TD><TD class=xl27 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl30 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl30 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl33 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: blue"> </TD></TR></TBODY></TABLE>

Does that make sense?

#### weegieavlover

##### Board Regular
Any suggestions?

the formula in Column I:

=ISNUMBER(MATCH(G1,A1:A10,0))*ISNUMBER(MATCH(H1,B1:B10,0))

Is there anyway to get this formula to insert the 1 or 0 into column C.

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,164,674
Messages
5,838,711
Members
430,566
Latest member
ChanchalSingh

### 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.

### Which adblocker are you using?

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