This post is related to the following video:
https://www.youtube.com/watch?v=YWfzYu59Gmo&lc=Ugw25_12awm5go-pllN4AaABAg.99PlXYa_Hwd99PphnQEYoY
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.