Array lists all values against a criteria

• The attached spreadsheet forum.ozgrid.com/index.php?attachment/55851/ has an array in cell M2:
{=IF(ISERROR(INDEX(\$B\$3:\$F\$22,SMALL(IF(\$B\$3:\$B\$21=STAFF,ROW(\$F\$3:\$F\$21)),ROW(1:1)),5)),"",INDEX(\$B\$3:\$F\$22,SMALL(IF(\$B\$3:\$B\$21=STAFF,ROW(\$F\$3:\$F\$21)),ROW(1:1)),5))}
(This is copied down to M5, with the Row number changing automatically)

The formula SHOULD look in Col B for all instances of the name in M1 (“STAFF”), and list the corresponding values from Col F into M2 – M5.

Unfortunately it isn’t working.

• The name “DR” occurs three times in Col B, but the array shows only two instances.
• Whichever name is selected, all the values are “0” and not the actual values in Col. F.

Any pointers received gratefully.
Ochimus

Files

• Re: Array lists all values against a criteria

You need to add the -ROW(\$F\$3)+1 part to get the row count to start at 1..... and also because you have .xlsx file, you can shorten the formula.....

Try:

=IFERROR(INDEX(\$B\$3:\$F\$22,SMALL(IF(\$B\$3:\$B\$21=STAFF,ROW(\$F\$3:\$F\$21)-ROW(\$F\$3)+1),ROW(1:1)),5),"")

confirmed with ctrl+shift+enter and copied down.

Where there is a will there are many ways. Finding one that works for you is the challenge!

• Re: Array lists all values against a criteria

Drad down and accros Confirm Contrl+Shift+Enter
=IFERROR(INDEX(\$F\$2:\$F\$21,SMALL(IF(\$B\$2:\$B\$21=O\$1,ROW(\$B\$2:\$B\$21)-ROW(\$B\$2)+1),ROWS(\$B\$2:B2))),"")

• Re: Array lists all values against a criteria

Many thanks to both of you for the prompt amendments. Have to "mug up" on the "Row+1" concept, which I admit freely baffles me. Clearly a "two bottle" problem.

• Re: Array lists all values against a criteria

Array formulas(the Indexing one) have to have an offset to give you next availabe values
If we use
ROW(\$B\$2:\$B\$21)-ROW(\$B\$2)
That wold give
0,1,2,3,...
As there is no 0 row you would end up with #REF error.
+1 make this array 1,2,3,4

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!