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" <mrnw31666@blue.co.uk> wrote in message
news:XxMHe.81349$G8.48398@text.news.blueyonder.co.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" <roger@technologyNOSPAM4u.co.uk> wrote in message
> news:%23%23r0GY3lFHA.1372@TK2MSFTNGP10.phx.gbl...
>> Hi Phil
>> In cell AA1
>> =AND(A1="phil",B1="Tony")+AND(A1="Tony",B1="Phil")*1
>>
>> --
>> Regards
>>
>> Roger Govier
>>
>>
>> "Phil" <someone@home.sometimes.uk> wrote in message
>> news:o5MHe.81329$G8.58682@text.news.blueyonder.co.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
>> >
>> >
>> >
>> >
>> >
>>
>>
>
>