# Excel 365 Wildcard Vlookup to Return All Partial Matches

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.

Excel Version: Excel 365

#### Question? Ask it in our Excel Forum ##### Excel VBA Course - From Beginner to Expert

200+ Video Lessons 50+ Hours of Instruction 200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

Similar Content on TeachExcel
How to Quickly Find Data Anywhere in Excel
Tutorial: Finding specific records and/or cells is easy when using the Find tool in Excel. It is lo...
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...
Tutorial Details
Excel Version: Excel 365 