Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
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.)

View Course

Wildcard Vlookup to Return All Partial Matches in Excel

2

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.

Answer
Discuss

Discussion

Glad you managed to get some solutions! Formulas really are their own language it seems. Would you be interested in submitting this as a tip on the site so that it can be more easily found/used by visitors? If you login and click Submit a Tip in the upper left of the window, you can do it there - I could also do it but I didn't want to take any credit away from you. Otherwise, it can just live here as well)
don (rep: 1989) Jun 3, '20 at 7:21 am
Sure thing. Those new functions make extremely easy.
schardt679 (rep: 4) Jun 3, '20 at 5:23 pm
What category should I use? Also there is no option for Office 365 for version, and there's no option for XLOOKUP and Filter for functions used
schardt679 (rep: 4) Jun 4, '20 at 1:07 am
Sorry for the late reply, I just kind of forgot to get everything rolling with this - the system does need to be updated for the new versions of Excel, including the functions list that you can check. I will need to freshen-up the tutorial submit feature soon, sorry about that. Thank you for the submission! I'm hoping to be able to increase collaboration on here one day so we can have a truly helpful Excel resource. I'm open to any suggestions you have regarding the tutorial submission and editing process, which I am sure is a bit clunky now.
don (rep: 1989) Jul 22, '20 at 5:49 am
Add to Discussion

Answers

0

Here is the published tutorial:

Excel 365 Wildcard Vlookup to Return All Partial Matches

I made just a few edits to it and embedded the original video.

Discuss


Answer the Question

You must create an account to use the forum. Create an Account or Login