Excel 365 Wildcard Vlookup to Return All Partial Matches

Add to Favorites
Author: | Edits: don

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.

Question? Ask it in our Excel Forum


Excel Function: ISNUMBER(), SEARCH, SEARCHB()
Downloadable Files: Excel File

Similar Content on TeachExcel
Vlookup to Return All Matching Results
Tutorial: Here is an Excel formula that will act like a Vlookup that returns every matching result ...
Vlookup to Return the Min, Max, or Average Value in Excel
Tutorial: Perform a Vlookup that returns the highest value, lowest value, or average value from a d...
Vlookup Macro to Return All Matching Results from a Sheet in Excel
Macro: This Excel Macro works like a better Vlookup function because it returns ALL of the matchi...
Vlookup Macro to Return All Matching Results and Stack them with Previous Results
Macro: This is very similar to the other Vlookup type Macro in that it returns all of the results...
Excel Macro that Searches Entire Workbook and Returns All Matches
Macro: This is the ultimate Lookup Macro for Excel. It will search every worksheet in the workboo...