Explanation
This formula uses the FILTER function to retrieve data based on a logical test constructed with the LARGE and IF functions. The result is the top 3 scores in group B.
The FILTER function applies criteria with the include argument. In this example, criteria are constructed with boolean logic like this:
(score>=LARGE(IF(group="b",score),3))*(group="b")
The left side of the expression targets scores greater than or equal to the 3rd highest score in group B:
score>=LARGE(IF(group="b",score),3)
The IF function is used to make sure LARGE is only working with group B scores. Because we have 12 scores total, IF returns an array with 12 results like this:
{FALSE;65;FALSE;80;FALSE;88;FALSE;76;FALSE;86;FALSE;83}
Notice the only scores that survive the operation are from Group B. All other scores are FALSE. This array is returned directly to LARGE as the array argument:
LARGE({FALSE;65;FALSE;80;FALSE;88;FALSE;76;FALSE;86;FALSE;83},3)
LARGE ignores the FALSE values and returns the 3rd highest score, 83.
We can now simplify the formula to:
=FILTER(data,(score>=83)*(group="b"))
which resolves to:
=FILTER(data,{0;0;0;0;0;1;0;0;0;1;0;1})
Finally, FILTER returns records for Mason, Annie, and Cassidy, which spill into the range F5:H7.
Sort results by score
By default, FILTER will return matching records in the same order they appear in the source data. To sort results in descending order by score , you can nest the original FILTER formula inside the SORT function like this:
=SORT(FILTER(data,(score>=LARGE(IF(group="b",score),3))*(group="b")),3,-1)
Here, FILTER returns results directly to the SORT function as the array argument. Sort_index is set to 3 (score) and sort_order is set to -1, for descending order.
Explanation
This formula relies on the FILTER function to retrieve data based on a logical test. The array argument is provided as B5:D14, which contains the full set of data without headers. The include argument is based on a logical test based on the ISNUMBER and SEARCH functions:
ISNUMBER(SEARCH("rd",B5:B14))
In brief, the SEARCH function is set up to look for the text “rd” inside the street data in B5:B14. Because this range includes 10 cells, 10 results are returned. Each result is either a number (text found) or a #VALUE error (text not found):
{#VALUE!;11;#VALUE!;#VALUE!;13;#VALUE!;#VALUE!;18;17;#VALUE!}
This array is delivered to the ISNUMBER function, which converts the result from SEARCH into an array that contains only TRUE or FALSE:
{FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;TRUE;TRUE;FALSE}
The array from ISNUMBER is provided to the FILTER function as the include argument, and FILTER uses this array to retrieve matching data. Only rows where the result is TRUE make it into the final output. The if_empty argument is set to “No results” in case no matching data is found.
Wildcards
The SEARCH function supports wildcards , so the filter logic can include these characters.
Case-sensitive
For a partial match, case-sensitive filter, you can adjust the formula to use the FIND function instead of SEARCH like this:
=FILTER(rng1,ISNUMBER(FIND("TXT",rng2)))
Note: FIND is case-sensitive, but does not support wildcards.
