How To Extract A Dynamic List From A Data Range Based On A Criteria Without Filters In Excel

In this tutorial we are going to see how we can use an array formula using IFERROR, INDEX, SMALL, IF and ROW functions to create a dynamic, filtered list without using any built-in Excel functions like Filter to accomplish this.
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:

[UpExcel]

Good to see the pre Excel 365 ways of doing stuff like this Mike and handy to know. Makes me appreciated the FILTER function we have now 👍

[thefast]

You the man! Took me two watches of the video but I have it crystal clear now. Thank you

[dckmusic]

Might be the most useful Excel video I've ever come across. Already put it to good use and will use it more in other projects.

[GeorgeAJululian]

thank you very helpful I watch the video three times thank you again

[louhill]

Mike...Thanks yet again! I looked at several articles/videos trying to explain this before I found your video. I was able to accomplish what I needed in just a few minutes!

[racershqpodcast]

This video is hot fire. Thanks for helping me figure this out.

[mmmperry]

Thank you. You explained it simply and it was easy to implement.

[MrTurnermason]

Thank you! this was the only video that actually went over it, the template download was really helpful

[merrillmchenry]

Great stuff. 1st time I've been to your site. I'll be back!

[timwheeler]

Very helpful (especially the multiple criteria add in below) and thank you!

[DW-zgoy]

Well explained and presented, thank you!

[wayneedmondson]

Hi Mike.. thanks for this tutorial.. great tips for creating dynamic filters with array formulas. Thanks for sharing your knowledge. Thumbs up!

[brianlasta]

Life saver! Thank you from the future.

[Lifeandchaos]

Thank you Mike, your video just helped me a lot.

[kennyo]

Great explanation, this is going to be super useful. Thanks so much for sharing!

[azizanmohdali]

Thanks Mike. Its great tutoriai. If we start our table not at row 1, let say we start at row 3, how did the k value of SMALL function should be. Really keen for explanation. Tq

[carolcoulter]

Really excellent explanation, thank you heaps!

[learningismust]

Thank your very much for sharing your knowledge with us... Its really help full :)

[amit]

This is great -- many thanks for this.

[DanielAbad]

tired formula, but my excel is not arraying the k value down once I hit crl+shift+enter all elements show it as ROW 1:1. Any advice?