Index Formula support

**markhorsey** · 07-12-2015, 04:48 PM

Hello

I am a school teacher and was wanting help with the following query. I prefer using formulas rather than macros. I have some knowledge of excel but not great.

I have 3 columns column
a - Level Achieved - Text
b - Child name- Text
C - This is where I need help - I want to this column to list all the children's names that = to a specific level - i.e. Emerging - I want it to list - Brian, Sally, Susan, Dave etc.

From searching on the internet and various forums people have suggested this formula but can't seem to get it to work

=IF(ISERROR(INDEX($A$2:$B$63,SMALL(IF($A$2:$A$63=$C$1,ROW($A$2:$A$63)),ROW(1:1)),2)),"",INDEX($A$2:$B$63,SMALL(IF($A$2:$A$63=$C$1,ROW($A$2:$A$63)),ROW(1:1)),2))

As it does not display the correct children's names. I have highlighted the names it should highlight but the list created in column C is different.

Please help!

Much appreciated

Mark

**FDibbins** · 07-12-2015, 05:01 PM

Hi, welcome to the forum

You were almost there

I have made a few small adjustments, but you pretty much had it. That is an ARRAY formula
...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

=IFERROR(INDEX($B$2:$B$63,SMALL(IF($A$2:$A$63=C$1,ROW($A$2:$A$63)-1),ROWS($A$1:A1))),"")
copied down and across

**Tony Valko** · 07-12-2015, 05:12 PM

Here's another way...

This array formula** entered in C2:

=IFERROR(INDEX($B:$B,SMALL(IF($A$2:$A$61=C$1,ROW(A$2:A$61)),ROWS(C$2:C2))),"")

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Copy across to E2 then down until you get a row full of blanks.