cross referencing

P

Phil

I am trying to find a formula that looks at a range of cells, a2:b11, and if
a cell in the range has a value Phil and its adjacent cell in the other
column
has a value Tony then AA1=1, if no adjacent cells match the criteria AA1
=0. The names can be in either column.

For instance...

A B
Phil Tony would give AA1 value 1
Tony Phil would also give AA1 value 1
xxx yyy would give AA1 value 0

Thanks
 
P

Phil

Thanks, that works if the values Phil and Tony are in A1 or B1 but they
could be anywhere from A2 : A11 or B2 : B11
 
R

RagDyeR

What happens if you have *multiple* occurrences of "Tony" and "Phil"?

See if this works for you.

Will count *all* occurrences of "Tony" and "Phil" being on the same line
(row):

=SUMPRODUCT((A2:B11={"Tony","Phil"})*(A2:B11={"Phil","Tony"}))

--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================

Thanks, that works if the values Phil and Tony are in A1 or B1 but they
could be anywhere from A2 : A11 or B2 : B11
 
R

Roger Govier

Hi Phil

The following array formula works for me. To enter an array formula, press
Crontrol+Shift+Enter after the final ).
Do not enter the { } brackets, Excel will do this for you.
=IF(SUM((A1:A11="Phil")*(B1:B11="Tony")*1)+SUM((A1:A11="Tony")*(B1:B11="Phil")*1),1,0)
 
P

Phil

Tony and Phil will only appear once
RagDyeR said:
What happens if you have *multiple* occurrences of "Tony" and "Phil"?

See if this works for you.

Will count *all* occurrences of "Tony" and "Phil" being on the same line
(row):

=SUMPRODUCT((A2:B11={"Tony","Phil"})*(A2:B11={"Phil","Tony"}))

--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================

Thanks, that works if the values Phil and Tony are in A1 or B1 but they
could be anywhere from A2 : A11 or B2 : B11
Click to expand...
 
P

Phil

Thanks, that works OK. It a bit different to one i had , but stupidly lost
after a system crash, though. That , as far as i remember looked something
like....
=if(A2:B11=("Phil:Tony"),1,if(A2:b11=("Tony:phil"),1,0)
I know thats not right cos it dont work.

Thank aagain
 
Top