array formula returning 'circular reference' error

[palaeontology]

Hello to all,

I have the following sheet, called ... Full School List ...

I have another sheet called ... Bradman ...

In BradmanB10 I have the following array formula ...

{=IFERROR(INDEX('Full School List'!$K$2:$K$2500, MATCH(0, COUNTIF(B$10:B10, 'Full School List'!$K$2:$K$2500)+IF('Full School List'!$F$2:$F$2500<>B$5, 1, 0)+IF('Full School List'!$S$2:$S$2500<>B$6, 1, 0), 0)),"")}

but as you can see in the image it's returning zeros, with the error message ...

The formula is meant to return the surnames of students (from column K in the 'Full School List' worksheet) who meet two conditions ...
1. they are in yr 7
2. they are female

When I evaluate the formula, i get down to the following final step ...

and it shows that the surname Adams is the first name that should appear in the list (as it has met both criteria), and this is correct, but it then goes and returns a zero.

Can anyone see where I've gone wrong ?

Kind regards,

Chris

 

[J.Ty.]

Your formula sits in B10 and it references B10:
{=IFERROR(INDEX('Full School List'!$K$2:$K$2500, MATCH(0, COUNTIF(B$10:B10, 'Full School List'!$K$2:$K$2500)+IF('Full School List'!$F$2:$F$2500<>B$5, 1, 0)+IF('Full School List'!$S$2:$S$2500<>B$6, 1, 0), 0)),"")}
Therefore you get the circular reference error.

J.Ty.

 

[palaeontology]

so simple, thankyou for seeing that.

Very kind regards,

Chris

 

[J.Ty.]

If you encounter a similar problem, go to Ribbon->Formulas, and look at the tool for handling errors. One of its options is to indicate circular references.

J.Ty.

 

[palaeontology]

I didn't know that, thankyou.

Ok, I've added a third criteria to be met, so the 3 criteria are now ...

1. they are in yr 7 .. this is found in 'Full School List'!$F$2:$F$2500, and checked vs C$5
2. they are female .. this is found in 'Full School List'!$S$2:$S$2500, and checked vs C$6
3. they are in Brd (which is Bradman House) ... this is found in 'Full School List'!$G$2:$G$2500 and checked vs $M$2

however, I realise now, my formula is extracting a list of unique names, so if a student surname has been extracted that should also be extracted further down as well (in the case of twins, for example) my formula is skipping that 2nd occurence of the surname and moving on to find the next unique name that meets the three criteria.

How can I adjust my formula to NOT extract unique names, but ALL names that meet the 3 criteria ?

This is the formula currently ...

=IFERROR(INDEX('Full School List'!$K$2:$K$2500, MATCH(0, COUNTIF(C$9:C9, 'Full School List'!$K$2:$K$2500)+IF('Full School List'!$F$2:$F$2500<>C$5, 1, 0)+IF('Full School List'!$S$2:$S$2500<>C$6, 1, 0)+IF('Full School List'!$G$2:$G$2500<>$M$2, 1, 0), 0)),"")

 

[J.Ty.]

What is your Excel version? In particular, do you have FILTER function?

 

[J.Ty.]

If you have those functions, the task becomes much, much simpler.
If you don't, you can use
{=IFERROR(INDEX('Full School List'!$K$2:$K$2500, MATCH(0, (COUNTIF(C$9:C9, 'Full School List'!$K$2:$K$2500)=COUNTIF( 'Full School List'!$K$2:$K$2500, 'Full School List'!$K$2:$K$2500))+IF('Full School List'!$F$2:$F$2500<>C$5, 1, 0)+IF('Full School List'!$S$2:$S$2500<>C$6, 1, 0)+IF('Full School List'!$G$2:$G$2500<>$M$2, 1, 0), 0)),"")}

 

[palaeontology]

Unfortunately that didn't work.

it returned the following ..

the only changes I made to your formula was to change the 'C' column references to 'G' column, so that shouldn't have caused the error.

 

[J.Ty.]

I do not have your data, so I cannot recognize if this is an error or not.

 

[palaeontology]

The data list it's pulling from had no repeated surnames, so there should have only been one of each of those names