Error: A function in this formula causes the result to change ...

I am allowing students to choose three students with whom they wish to work. So Student A might choose Sudents B, C and D. I need to determine if any or all of B, C and D also chose A so that I can pair them up. There are about 60 students in the mix. I am using the following formulae (thanks to Nimrod) to test each student's 3 choices:

=MATCH(A1,INDIRECT(MATCH(B1,A:A,0) & ":" & MATCH(B1,A:A,0)),0) - 1
=MATCH(A1,INDIRECT(MATCH(C1,A:A,0) & ":" & MATCH(C1,A:A,0)),0) - 1
=MATCH(A1,INDIRECT(MATCH(D1,A:A,0) & ":" & MATCH(D1,A:A,0)),0) - 1

In general it appears to work but for some cells I get unexpected results.

I used EVALUATE FORMULA to establish what was happening and found that #N/A-1 often returns the value '1' or "2" or "3". I get the message: 'A function in this formula causes the result to change each time the spreadsheet is calculated. The final evaluation step will match the result in the cell, but interim steps may not.'

Any ideas as to what is happening? Ta!
Sally27

PS It also says that I have a circular argument, but it's seems to be because B picks D who picks G who picks B, and that is as it should be.

The INDIRECT() function is the one that calculates every time the sheet calculates. It is referred to as a volatile function. See this link for more on that:

http://www.decisionmodels.com/calcsecretsi.htm


When I was in school (a long time ago) my teachers would say to us "form groups of 4" and it got done. No spreadsheet needed.