How To Extract Multiple Data In Multiple Cells Excel

In this tutorial nosotros are going to use an array formula to excerpt a dynamic listing from a data prepare based on multiple criteria. This is in response to a question I had on previous posts, which I'll list below, where I used merely a single criteria.

You tin download the file here and follow along. When you go a preview, await for Download in the upper right mitt corner.

The two previous posts on this topic tin exist establish here and here.

In our previous posts, we had a list of guests, whether they were attention an event and if they made a donation:

We wanted to generate ii lists: ane that indicated who was coming or non coming based on the criteria in cell K1 and some other who had made donations greater than a sure amount indicated in cell L1:

The formula in column E is:

{=IFERROR(Alphabetize($A$2:$A$26,SMALL(IF($B$2:$B$26=$K$one,ROW($A$two:$A$26)),
ROW(1:1))-1,one),"")}

And the formula in cavalcade H is:

{=IFERROR(Alphabetize($A$2:$A$26,SMALL(IF($C$2:$C$26>=$50$1,ROW($A$ii:$A$26)),
ROW(1:1))-1,1),"")}

These are array formulas – note the curly brackets (which must exist entered using Ctrl + Shift + Enter). I'chiliad non going to get through the procedure of how these piece of work, you can view the tutorials that I linked to above. But discover the only deviation in these two are the logical examination of the IF statement:

$B$2:$B$26=$K$1 vs $C$ii:$C$26>=$Fifty$1

So, if we wanted to create a formula with multiple criteria, ane idea might be to apply the AND part in the IF statement to combine the two criteria, like this:

{=IFERROR(INDEX($A$2:$A$26,SMALL(IF(AND($C$two:$C$26>=$L$ane,$B$ii:$B$26=$K$one),
ROW($A$2:$A$26)),ROW(1:one))-1,1),"")}

Notwithstanding, the issue will be a blank cell (the upshot coming from the IFERROR function that says, "if there is an error, return a blank").

The reason is quite basic. In Excel the AND function reviews all the logical tests and will only return a single TRUE or FALSE based on the fact if all the arguments are TRUE or if i or more than is FALSE. The AND function will never generate a list of results, like y'all need in an array formula.

So let's wait at an alternative to this. Hither is where we want to generate the list of results based on the criteria in cells H1 and I1:

The formula we will use in prison cell E2, and copy down, will exist:

{=IFERROR(Alphabetize($A$two:$A$26,SMALL(IF(($B$ii:$B$26=$H$1)*($C$2:$C$26>=$I$one),
ROW($A$ii:$A$26)),ROW(one:1))-1,1),"")}

Discover that we replaced the erroneous AND function with:

($B$2:$B$26=$H$one)*($C$2:$C$26>=$I$ane)

What we did here was take the ii (or there could be more) criteria and multiply them past each other. What volition happen hither is this. Excel volition take the first criteria and generate a list of TRUES and FALSES, which to Excel are ones and zeros. So it volition take the second criteria and practice the same. Then now nosotros have a series of ones and zeros that are multiplied past each other, and volition result in ones when a one is multiplied by a ane, simply a nothing in any other case:

This volition result in a concluding list of 1s and 0s that will and so return the row numbers only for those where at that place is a one.

At present nosotros're back to the SMALL part which will return the 1st, 2nd, tertiary, etc. smallest row number for the INDEX function that volition provide united states of america with the list of names for our list.

Take a few minutes to watch the video, and the videos for the previous tutorials to amend understand how the overall formula works.

What tin you do next?
Share this post with others that can benefit from information technology!
Leave a comment or reply below – allow me know what yous think!
Subscribe to this blog for more slap-up tips in the futurity, or…
Check out my YouTube channel!

Happy Excelling!