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.

You can download the file here and follow along. If you get a preview, look for the download arrow in the upper right hand corner.

Here I have a list of guests, if they are coming to our event and whether they made a donation:

I want to be able to pull into this area only those that meet the criteria we have in cell K1, which in this case is a “Y”:

I also want to pull into this area only those that gave a donation greater than the value I have in cell L1, which in this case is $0, which means anyone who gave a donation:

I also want these to be dynamic so that if we make a change to either cell K1 or L1 or the data in the main guest list, the lists adjust automatically, and show no blank rows in between, nor any error results.

Here are the functions and their syntax that we are going to use in our formula:

This is an array formula, which is defined as follows:

And here is the formula that we used in cell E1 to extract the first item in our list:

{=IFERROR(INDEX($A$2:$A$26,SMALL(IF($B$2:$B$26=$K$1,ROW($A$2:$A$26)),ROW(1:1))-1,1),””)}

There is a bit of complexity to this process, along with the addition of some VLOOKUP formulas to assist us. Please take a few minutes to watch the video to get the best explanation of how this works.

What can you do next?
Share this post with others that can benefit from it!
Leave a comment or reply below – let me know what you think!
Subscribe to this blog for more great tips in the future!
Check out my YouTube channel – click on the YouTube icon below!

Happy Excelling!

Related Post

Free Download!

Subscribe to Download Your FREE Copy of
"My 70+ Favorite Excel Keyboard Shortcuts" Today!

, , , , , , ,

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

  1. Julie Willey January 31, 2017 at 1:42 pm #

    Thanks Mike, This is helpful. I can already think of few items that I will use this one.

Leave a Reply