Excel 365 Wildcard Vlookup to Return All Partial Matches

This post is related to the following video:

TeachExcel explained how to perform a Vlookup (vertical search/return) for partial string matches and return all results using a non-Office 365 approach.

I wanted to try to make a dynamic array formula solution. Both TeachExcel's and my methods could be modified for horizontal lookups as well.

This is my Office 365 approach:

Non-dynamic Range:

=FILTER(B2:B8, ISNUMBER(SEARCH(D2, A2:A8)), "Not Found")

Dynamic Range:

=FILTER(B2:XLOOKUP("*", B:B, B:B, , 2, -1), ISNUMBER(SEARCH(D2, A2:XLOOKUP("*", A:A, A:A, , 2, -1))), "Not Found")

Column A is the lookup range, Column B is the return range, and D2 is the search criteria.

The non-dynamic range approach does not allow for easy range expansion (ranges must be updated) while the dynamic range approach updates automatically. The dynamic range should probably be a named range. The data could also be converted to a table.