Celebrate 'The VLOOKUP Book' birthday with us. Last day to get 50% discount on the e-book (31 October only).

Click here for details

Finding 2nd Largest Item in a List that meets a Criteria [Excel Array Formulas]

Posted on October 8th, 2010 in Learn Excel - 18 comments

Excel Array Formulas have subtle beauty and raw power. Today I want to share how you can use Excel Array Formulas to find-out the 2nd largest sale amount corresponding to a particular product.

Lets say you have sales data like this:

Second Largest Sale Item - using Array Formula - Excel Formula Tutorials

Now, we all know that, to get 2nd largest sale amount, we can use the LARGE() formula. Like this: =LARGE(RANGE,2)

But, how do we know what is the 2nd largest sale amount for the product “bricks”?

Now, that is tricky? Isn’t it?

This is when an array formula can help you.

First the formula:

Type below formula in a cell

=LARGE(IF(PRODUCTS_RANGE="Bricks",SALES_RANGE,""),2)

and press CTRL+SHIFT+ENTER.

Now, the explanation:

Instead of directly giving the SALES_RANGE to LARGE formula, we are asking excel to include empty spaces “” wherever product <> “Bricks” thru the IF() formula inside LARGE().

Now, when you press CTRL+SHIFT+Enter, excel treats the formula as array formula and processes the lists of values inside nicely.

Download Excel File with Array Formula Examples:

I have put together 4 simple examples to help you understand the power of array formulas. Please download the file from here.

How do you use array formulas?

While array formulas are powerful, they are also difficult to write and explain. I try to solve problems using non-array formula methods as much as possible. But in some cases, like above, I end up writing array formulas.

What about you? Do you use them? How differently would you solve the above problem? Please share using comments.

More examples on array formulas:

Your email address is safe with us. Our policies

Written by Chandoo
Tags: , , , , , , ,
Home: Chandoo.org Main Page
? Doubt: Ask an Excel Question

18 Responses to “Finding 2nd Largest Item in a List that meets a Criteria [Excel Array Formulas]”

  1. Rajatha says:

    Thanks Chanddoo…. never thought of this use of the formula :)

  2. sam says:

    Define cAmt = IF(PRODUCTS_RANGE=”Bricks”,SALES_RANGE)
    =Large(cAmt,2) no need of CSE

  3. Chandoo says:

    @Sam.. interesting idea. I did not know that named ranges would eliminate the need for CSE.

  4. Daniel Ferry says:

    @Chandoo –
    .
    All named formulas (named ranges included) are ARRAY formulas.
    .
    I delve into this in great detail in my new Excel Hero Academy, which starts in a few days!
    .
    http://www.excelhero.com/blog/2010/09/excel-hero-academy—interested.html
    .
    Regards,
    Daniel Ferry
    excelhero.com

  5. Rajesh says:

    Hello Chandoo:
    For example, if the second value is same (t0001-100, t0003-100) for 2 Transaction IDs, based on this large formula, i will get the first transaction id only 2nd one it will not come.
    In that what should do??? pl suggest.

    ~Rajesh~

  6. bill says:

    a couple of things:
    >what the heck does “CSE” stand for… maybe “Control/Shift/Enter”?
    >every month or so, Chandoo comes up with another simple, sweet, elegant “communication” of Excel features. This is yet another 15 carrot diamond in a long string of diamonds. Thank you…
    >i use indirect references a lot (as opposed to named ranges). my indirect reference is to a cell with a formula that extracts the sheet name and the cell range of the array through simple beg and end cell references. for me this works better than named references as the named range definition is once removed from view and named ranges are harder to house-keep (when you need hundreds of them).
    >the elegant formula that Chandoo has presented works for just about any formula i have tried it on. this must be the underlying formula structure for COUNTIFS, SUMIFS and AVERAGEIFS. wow!
    >this formula works even if the filters (“blocks”, “bricks”) are in independent reference cells which makes it very versatile for active dashboards and graphs.
    >not only does Chandoo’s formula work for SMALL and LARGE but also MIN, MAX, MEDIAN, STDEV, CORREL, QUARTILE, VAR, SUMSQ, etc. etc. again wow!
    >i have not been able to get this concept to work with LINEST, anyone know why?
    >i have not tried it yet with the matrix or financial formulas.
    bill

  7. Hui... says:

    @Bill
    CSE is as you suggested Ctrl Shift Enter

  8. bill says:

    i am having trouble getting the following to work…
    if i add a final column to the example dataset called PAID_RANGE and add either “paid” or “unpaid” to each row in that column then why won’t this formula work (for finding the largest block sale that has been paid):
    {=LARGE(IF(AND((PRODUCTS_RANGE=”Bricks”,SALES_RANGE,””),(PAID_RANGE=”paid”,SALES_RANGE,””))),1)}

  9. Chandoo says:

    @Bill: You can add additional conditions like this:

    {=LARGE(IF(AND(PRODUCTS_RANGE=”Bricks”,PAID_RANGE=”paid”),SALES_RANGE,””),1)}

    Both conditions go inside the AND() formula.

  10. Oli says:

    To solve this an avoid an array (CSE) formula I would use SUMPRODUCT:
    =SUMPRODUCT(LARGE((B2:B11=E2)*(C2:C11),2))

  11. bill says:

    @Chandoo: with regard to the example with an added criteria (“paid” vs “unpaid”), I tried rearranging the terms as you have suggested…
    {=LARGE(IF(AND(PRODUCTS_RANGE=”Bricks”,PAID_RANGE=”paid”),SALES_RANGE,””),1)}
    This works, sort of. If you examine each element of the array created within the formula, you find that when both filters are TRUE than you get the value from the SALES_RANGE array (this is as expected) but if one or more of the filters are FALSE then you get an error (#VALUE) assignment instead of the empty cell assignment (“”). So the result of this formula is always #VALUE even when entered in CSE fashion.

    This is a problem since almost all of the formulas (SMALL, LARGE, MIN, MAX, MEDIAN, STDEV, CORREL, QUARTILE, VAR, SUMSQ, etc.) work fine with an empty cell “” in the array but not with a #VALUE in the array. Also, note that for many of these listed formulas, zero can be a valid value so, I think, that the multiplicative (and additive approach for OR) would not work because zeros would be evaluated as array elements (for these example formulas).
    -bill

  12. Lee says:

    @Bill:

    {=LARGE(IF((PRODUCTS_RANGE=”Bricks”)*(PAID_RANGE=”paid”)*(ISTEXT(PAID_RANGE)),SALES_RANGE,FALSE),2)}

    I haven’t really tested it, but might work in that instance? CSE entered

  13. bill says:

    @Lee,
    >your suggestion works. you do have to use CSE. i do not get exactly why it works (yet), but it does. i tried it for two conditions. each element in the formula array evaluated to the cell value in SALES_RANGE or to a blank cell “” when both filer conditions were not met. >note i changed the FALSE argument to “” although it seems that the FALSE word gets treated the same as an empty cell for the LARGE, MIN, MAX, MEDIAN types of formulas).
    >i use the following formula to view the array value by value (this is the IF statement with an INDEX formula substituted for the LARGE formula overwrap):
    {=INDEX(IF((PRODUCTS_RANGE=”Bricks”)*(PAID_RANGE=”paid”)*
    (ISTEXT(PAID_RANGE)),SALES_RANGE,””),n,1)}
    where n=row number in array
    thank you!
    -bill

  14. Kenneth says:

    Thank you, this is very useful.  I am curious if there’s an efficient way using just formulae (no VBA/macros/addins) to return the transaction ID instead of the amount?

    To expand on the example in the article, imagine the list of transactions is ~200,000 rows.  I want to list the top 10 transactions based on quantity of Bricks sold.  I don’t want the formula to return the #1 largest quantity of bricks sold, I want it to return the transaction ID that corresponds to the #1 quantity of bricks sold.

    Obviously I can put the source data into a table and use filtering & sorting to see this, but in addition to Bricks and Blocks I also sell 30 other things, so it’s a big time saver for me if Excel will list my top 10 transaction IDs for each item sold rather than me having to work the filters and extract the data myself.

    I hope that makes sense.

  15. kp says:

    You can try =large(C2:C6 * (B2:B10 = “Bricks”),2) and Ctrl+Enter
     

  16. car scratch repair Leeds says:

    I needed to thank you for this fantastic read!!
    I definitely enjoyed every little bit of it. I’ve got you book marked to look at new things
    you post…

  17. Atul Dond says:

    Dear Chandoo,

    There is one issue with this formula that it is not working properly when i drag this to consecutive rows for large data.

    Kindly guide on this.

    Thanks
    Atul Dond

Leave a Reply