Array lists all values against a criteria

Ochimus · Aug 20th 2013

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

**NBVC** · Aug 20th 2013

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.

Robert Mika · Aug 20th 2013

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))),"")

Ochimus · Aug 20th 2013

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.

Robert Mika · Aug 20th 2013

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

Array lists all values against a criteria

Participate now!

Share