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

Posted on October 8th, 2010 in Learn Excel - 26 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:

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?

### 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.

### 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.

### Introducing our Online Power BI Class:

Would you like to join me on a date with Power BI? In this comprehensive online class, learn all about Power BI so you can create beautiful, insightful & interactive reports. Join me and rest of the play mates for our first ever Power BI Play Date.

 Bad News & VLOOKUP Contest Insert Currency Symbols & Other Special Characters in Excel [Quick Tip]
 Written by Chandoo Tags: advanced excel, array formulas, downloads, if() excel formula, large, Learn Excel, Microsoft Excel Formulas, quick tip Home: Chandoo.org Main Page ? Doubt: Ask an Excel Question

### 26 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!
.
.
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~

• Kalop says:

Hi, Rajesh

Did you get any suggestion to your problem?
I am interesting in that because i have the same problem.
If first and second value is the same it will show only the first, but what about the second???

• Kenneth says:

One option is to add a column with the row # divided by 10000 added to the significant value, then have the array formula target that column instead of the significant value.

So if you want to return the top 10 values of column A, have column F be +row()/10000. Then instead of telling it to rank the top 10 values of column A, have it rank the top 10 values of column F. That way if there's a tie in A it will be different values in F because 1/10000th of the row number is added.

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:

{=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))

• DonW says:

I know this is a couple of days off from Oli's post and I apologize for my tardiness, but, in his SUMPRODUCT example above, what is "E2"?

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.

• Chr says:

Hi, I'm facing the same problem too here. Is there anyone who can solve this? thank you.

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

• Hui... says:

@Atul

Hui...

18. MM says:

Hi
I am trying to use this formula in a vba Code but it is not working. My code is something like: ActiveSheet.WorksheetFunction.FormulaArray = "=LARGE(IF(F2:F9000="Bricks",G2:G9000),10)"

• KW says:

ActiveSheet.WorksheetFunction.FormulaArray = "=LARGE(IF(F2:F9000=""Bricks"",G2:G9000),10)"

Bricks in double quotes

19. Dat says:

Hi Chandoo,

I have downloaded your example spreadsheet and for some reason after I clicked on the cells that you have the formulas in(G6 & G7), it turns in to "#Value." Could you please let me know what that could mean? I think there is something wrong with my setting. Thank you.

20. Wei Loon says:

Hi Chandoo,

Thanks for the info above. But what if say using the same data set, I wanted to know what product gives the highest sales amount and the total of the highest sales product amount?

Or say there is an additional column of a Month. In which month the sales is highest and which product?

To simplify, on which month, which product produces the highest sales amount?

Is this possible?

 Bad News & VLOOKUP Contest Insert Currency Symbols & Other Special Characters in Excel [Quick Tip]