Extract a List of Values Filtered by Criteria with Sub-Arrays

Breaking Down the Sub-Array Formula Syntax

Taking each line in turn, we can understand what the formula does:

=IFERROR(

The IFERROR function just catches error messages, so the rest of the formula runs inside this parenthesis.

	INDEX('Car Data'!B$2:B$1156,

The INDEX function looks at an array to retrieve a specific row from inside it. The ‘Car Data’!B$2:B$1156 is the value field of INDEX, and it refers to the blue circled column of the original data table:

INDEX value

		SMALL(
			IF(

The SMALL and IF functions are inside the row_num field of the INDEX function. SMALL relies on the result of the IF function to specify its array field.

				'Car Data'!$H$2:$H$1156>='Filtered List'!$A$2,

This is the logical_test field of the IF function. It compares each row in the array ‘Car Data!$H$2:$H$1156 (circled below in orange) to the value in ‘Filtered List’!$A$2 (circled below in purple).

IF logical_test Subject

IF logical_test Criteria

				ROW('Car Data'!B$2:B$1156)-ROW('Car Data'!B$2)+1

When the rows in orange above are greater than or equal to the value circled in purple, the IF statement will calculate the row number using the above formula. The ROW functions work together to look up the number of the row within the array ‘Car Data!$H$2:$H$1156 and then subtract any rows that are above it in the worksheet. Look at the example below to see how the combination of ROW functions calculates the number of the row within the array instead of the number of the row in the spreadsheet.

ROW Demonstration

			),
			ROWS('Car Data'!B$2:'Car Data'!B2)

The first parenthesis closes the IF statement, meaning that there is no action if the IF statement is FALSE. The ROWS function tells the SMALL function which item in the array to return for the sub-array. Look at the example below to see how the function works:

ROWS Demonstration

		)
	)
,"")

The first two parentheses close the SMALL and INDEX functions. This means that SMALL is controlling what values the INDEX function displays. The final line of the formula is the value IFERROR displays when an error is found (a blank space). This is useful when we drag down our sub-array formula.