Breaking Down the Sub-Array Formula Syntax
Taking each line in turn, we can understand what the formula does:
The IFERROR function just catches error messages, so the rest of the formula runs inside this parenthesis.
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:
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).
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.
), 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:
) ) ,"")
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.
Get the latest Excel tips and tricks by joining the newsletter!
Andrew Roberts has been solving business problems with Microsoft Excel for over a decade. Excel Tactics is dedicated to helping you master it. You can read more of his writing on his personal blog at NapkinMath.io.
Join the newsletter to stay on top of the latest articles. Sign up and you'll get a free guide with 10 time-saving keyboard shortcuts!
Other posts in this series...
- VLOOKUP with Multiple Values or Criteria Using INDEX and MATCH (How To)
- Faster Multiple Criteria Lookups with VLOOKUP and CONCATENATE
- Extract a List of Values Filtered by Criteria with Sub-Arrays
- How to Combine (Concatenate) Data from Multiple Rows into One Cell
- How to Unpivot Columns into Rows in Excel Using Power Query