INDEX and SMALL only returning one result

Question

I have the below Excel formula which I have been writing which has an error. =IFERROR(INDEX([Testbook2.xlsx]Sheet1!$A$2:$B$6,SMALL(IF([Testbook2.xlsx]Sheet1!$B$2:$B$6="A",ROW([Testbook2.xlsx]Sheet1!$A$2:$A$6)-ROW([Testbook2.xlsx]Sheet1!$A$2)+1),ROW([Testbook2.xlsx]Sheet1!1:1)),1),"No Active Volunteers")

The code is supposed to look in another workbook (Testbook2) in the column where the text entered is supposed to be an A or an I. It is then supposed to return all the names with an A next to them. However it only returns the first name.

Any ideas on why this may have occurred?

Answer 1

I'm not a really big fan of making formulas more complicated than they need to be. The purpose of the ROW function is to produce a series of sequential numbers. It is used to note the position within a range, not the actual row on the worksheet. It doesn't matter where the row references; it does not have to reference the workbook/worksheet where you are collecting data.

These,

ROW([Testbook2.xlsx]Sheet1!$A$2:$A$6) - ROW([Testbook2.xlsx]Sheet1!$A$2) + 1
ROW([Testbook2.xlsx]Sheet1!1:1)

... can be shortened to,

ROW($1:$5)
ROW(1:1)

There are 5 cells in [Testbook2.xlsx]Sheet1!$A$2:$A$6 so all you require is a number between 1 and 5. The ROW(1:1) is a 1 that will become a 2 then a 3, etc as it is filled down. It provides the k parameter of the SMALL function. It doesn't matter where it comes from.

Once I got those pared down to what was actually necessary, it became evident that your formulas was working in its current state. However, it is an array formula, not an SQL formula.

=IFERROR(INDEX([Testbook2.xlsx]Sheet1!$A$2:$A$6, SMALL(IF([Testbook2.xlsx]Sheet1!$B$2:$B$6="A", ROW($1:$5)), ROW(1:1))), "No Active Volunteers")

Array formulas need to be finalized with Ctrl+Shift+Enter↵. Once entered into the first cell correctly, they can be filled or copied down or right just like any other formula. Without the special CSE finalization, you will only get the first value and then errors after that.

If you are seeking a standard formula that does not require CSE then the AGGREGATE¹ function function can provide pseudo-array-like processing without having to use CSE.

=IFERROR(INDEX([Testbook2.xlsx]Sheet1!$A$2:$A$6, AGGREGATE(15, 6, ROW($1:$5)/([Testbook2.xlsx]Sheet1!$B$2:$B$6="A"), ROW(1:1))),"No Active Volunteers")

¹ _{The AGGREGATE function was introduced with Excel 2010. It is not available in earlier versions.}

For pre-2010 versions of Excel, this standard formula does not require CSE either.

=IFERROR(INDEX([Testbook2.xlsx]Sheet1!$A$2:$A$6, SMALL(INDEX(ROW($1:$5)+([Testbook2.xlsx]Sheet1!$B$2:$B$6<>"A")*1E+99, , ), ROW(1:1))),"No Active Volunteers")

Now that you have decided to impart the actual workbook name, worksheet name and ranges that are to be used, here are working formulas,

As an array formula (with all of the ranges the same size),

=IFERROR(INDEX('[Volunteer Contact list July 2015.xlsm]Volunteers'!$A$2:$A$65, SMALL(IF('[Volunteer Contact list July 2015.xlsm]Volunteers'!$Q$2:$Q$65="A",ROW($1:$64)),ROW(1:1))),"No Active Volunteers")

As an standard formula (with all of the ranges the same size),

=IFERROR(INDEX('[Volunteer Contact list July 2015.xlsm]Volunteers'!$A$2:$A$65, SMALL(INDEX(ROW($1:$64)+('[Volunteer Contact list July 2015.xlsm]Volunteers'!$Q$2:$Q$65<>"A")*1E+99, , ), ROW(1:1))),"No Active Volunteers")

Enter each into the cell appropriately for the type of formula it is. Fill down as necessary for subsequent records.