Can a formula do this?

[weegieavlover]

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

 

[Aladin Akyurek]

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.

Why don't you try the formula as given to you?

Enter in C1 and apply control+shift+enter:

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

Control+shift+enter: Press the control and shift key while you hit enter.

 

[energman58]

Unless i have missed something putting the formula in C instead of I would work or in C1 put =I1 etc.

Or is there something more subtle at work here?

 

[T. Valko]

Enter in C1 and apply control+shift+enter:

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

Control+shift+enter: Press the control and shift key while you hit enter.

Array entered**:

=MAX(IF($A$1:$A$10=G1,IF($B$1:$B$10=H1,1)))

** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER.