Designing the Sub-Array Formula

Then we need to design the formula that will pull the rows of the table that meet our criteria. This formula will be broken down into its individual functions so we can walk through each part in turn. The sub-array formula is built with the following functions:

• IFERROR
• INDEX
• SMALL
• IF
• ROW
• ROWS

The IFERROR function is just a security measure. It tries to run the formula inside it, and if any errors occur, it lets us control what happens instead of just outputting an error message. To learn more about how Excel handles errors, check out the Definitive Guide to Excel Error Types and Error Handling. The syntax for IFERROR is simple:

=IFERROR(value, value_if_error)

The rest of our sub-array formula will go inside the value field of the IFERROR function.

The INDEX function is responsible for pulling the information from the rows of data in our original table that meet our criteria. The way it knows which rows to show is based on the rest of the functions inside our formula. The syntax for INDEX is as follows:

=INDEX(array, row_num)

The array will be the column of data that matches our table header from the original data set. The row_num will be determined by the SMALL, IF, ROW, and ROWS functions.

The SMALL function extracts a specific item from the list of items we are calling an array. The syntax for SMALL is simple:

=SMALL(array, k)

The array will be determined by the IF and ROW functions. The item, k, will be determined by the ROWS function.

The IF function tests each row in our original data table to see if it meets our criteria. If it does, it will tell SMALL to give the item to INDEX and output it to our output table. The syntax for IF is as follows:

=IF(logical_test, [if_true])

The logical_test will include our criteria. The if_true will pass the row number to SMALL using the ROW function.

The ROW function just gives the active row number to the SMALL function when the IF statement is true. The syntax is simple:

=ROW([reference])

When there is no reference provided, ROW() returns the current row number.

The ROWS function tells the total number of rows in the sub-array to SMALL. The syntax is simple:

=ROWS(array)

All of these functions combine into an array formula that must be entered with a CTRL+SHIFT+ENTER instead of a normal ENTER command. This lets us provide the formula with entire ranges of cells instead of individual cell references and this is what allows the formula to work.

Using the Sub-Array Formula

The full syntax of the sub-array formula is complicated and a bit confusing, so we will break down each part individually. First, though, let’s take a look at the full formula for Cell A5 in our example. Cell A5 should have the Make of the first car in our original data that meets our Combined MPG criteria of 45. The formula for A5 is as follows:

{
=IFERROR(
	INDEX('Car Data'!B$2:B$1156,
		SMALL(
			IF(
				'Car Data'!$H$2:$H$1156>='Filtered List'!$A$2,
				ROW('Car Data'!B$2:B$1156)-ROW('Car Data'!B$2)+1
			),
			ROWS('Car Data'!B$2:'Car Data'!B2)
		)
	)
,"")
}

The function is surrounded by curly brackets {} automatically when you enter it as an array formula (CTRL+SHIFT+ENTER). You can select everything in between the brackets, paste it into cell A5 in the example, and enter it by pressing CTRL+SHIFT+ENTER.