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)

--
Regards

Roger Govier


"Phil" wrote in message
. uk...
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
"Roger Govier" wrote in message
...
Hi Phil
In cell AA1
=AND(A1="phil",B1="Tony")+AND(A1="Tony",B1="Phil") *1

--
Regards

Roger Govier


"Phil" wrote in message
. uk...
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