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 Version: Excel 365
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...
UNIQUE() Function - Excel 365 Function for Removing Duplicates
Tutorial: Excel 365 Formulas Course The new UNIQUE() function for Excel 365 allows you to quickly, ...