How To Extract A Dynamic List Based On A List Of Search Items In Excel

In this tutorial we are going to learn how to create a dynamic list of items based on a table of various items using the Search function in Excel.
To download the file and follow along, go to the link on my website at:

Do you need help with a formula or an Excel project?
Including a sample file is always helpful!

You can donate to my channel through any of the following:

PayPal:

Zelle or Venmo:

[wayneedmondson]

Hi Mike.. cool solution.. very clever! The helper column is really the key. I tried several ways to put it all in one formula, but there seems to be no way to have the list be dynamic in a single formula solution. Using the new FILTER function, I was able to do this: =FILTER(B2:B26, ISNUMBER(SEARCH(D2, B2:B26))+ISNUMBER(SEARCH(D3, B2:B26))+ISNUMBER(SEARCH(D4, B2:B26))+ISNUMBER(SEARCH(D5, B2:B26))). But, it requires a separate ISNUMBER/SEARCH combo for each of the list items and of course it sweeps everything in if one is blank. This also works against your helper column: =FILTER(B2:B26, A2:A26>0). Of course, you need Office/Microsoft 365 subscription EXCEL to be able to use the FILTER function. I'll keep noodling with this.. if I ever come up with a way to do it dynamically in one formula, I'll let you know. Until then.. helper column to the rescue! Thanks for sharing.. learned a lot. Thumbs up!!

[mattjones]

How to get the results onto another worksheet?