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:
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):
And the resulting array returned to the FILTER function as the include argument:
This array is used by the FILTER function to retrieve matching data. Only rows where the result is TRUE make it into the final output.
Finally, the if_empty argument is set to "No results" in case no matching data is found.
The SEARCH function supports wildcards, so the filter logic can include these characters.
For a partial match, case-sensitive filter, you can adjust the formula to use the FIND function instead of SEARCH like this:
Note: FIND is case-sensitive, but does not support wildcards.