Array Formula explanation

[Celticfc]

I have a working array formula which pulls names of our admins fromTable1 but I would like to know how it works.

[SIZE=2]=INDEX(Table1,SMALL(IF(Table1[Job]="Admin",ROW(Table1[Name])),ROW(1:1))-1,1)[/SIZE]

I’m just trying to understand 2 sections:


1) Whenever I move Table1 (currently A1:H90), I have toplay with the “-1”, why is this? Does Excel not know where the row starts forthis table anyway? Code:

ROW(1:1))-1

2) Regardless of which table column I use for the firstrow, it seems to work. What is the purpose of this section? Do I have tospecify a column name? Can I not use Row(1:1) here either? Code:

ROW(Table1[Name]))

Thank you in advance.

 

[Marcelo Branco]

This formula is not robust because it uses ROW(1:1)

Try this (assumes the first formula is inserted in J2 and copied down)
=IFERROR(INDEX(Table1[Name],SMALL(IF(Table1[Job]="Admin",ROW(Table1[Name])-MIN(ROW(Table1[Name]))+1),ROWS(J$2:J2))),"")
Ctrl+Shift+Enter

M.