Post by ZF on Apr 17, 2015 3:37:46 GMT -5
Select entire row in excel sheet if a specific value is contained within the row.
=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)
)
)
,"")
+
CTRL-SHIFT-ENTER
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:
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 to the value in ‘Filtered List’!$A$2.
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.
//Refer to diagram to understand the computation.
),
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:
//Refer to diagram for greater clarity
)
)
,"")
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.
SOURCE: exceltactics.com/make-filtered-list-sub-arrays-excel-using-small/4/
=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)
)
)
,"")
+
CTRL-SHIFT-ENTER
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:
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 to the value in ‘Filtered List’!$A$2.
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.
//Refer to diagram to understand the computation.
),
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:
//Refer to diagram for greater clarity
)
)
,"")
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.
SOURCE: exceltactics.com/make-filtered-list-sub-arrays-excel-using-small/4/