# Return 2nd largest value (ignoring duplicates) with criteria

1. ## Return 2nd largest value (ignoring duplicates) with criteria

I tried so long to get this on my own, then I searched the web for hints, and as a last resort I am posting it up here myself!

So here is some example data:

 A B 1 25 1 40 1 40 2 25 2 16 2 35 2 35 2 30 2 13

Now say I want the 2nd largest number from column B but only if there is a 2 in column A. The answer in this case should be 30.

I've used many different combinations of LARGE, IF, COUNTIF, MAX, SUMPRODUCT, Ctrl+Shift+Enter, etc.

This was my most recent attempt:

=SUMPRODUCT(LARGE((A2:A10=2)*(B2:B10),COUNTIFS(A2:A10,2,B2:B10,MAX(B2:B10))+1))

I know that the following formula works if I need the 2nd largest number with no duplicates:

=LARGE(B2:B10,COUNTIF(B2:B10,MAX(B2:B10))+1) which should give 35 in my example and it does

My problem comes into play when I try to add the criteria.

Any help would be greatly appreciated (fix my formulas or give me a totally different formula)  Register To Reply

2. ## Re: Return 2nd largest value (ignoring duplicates) with criteria

Hi Sqwirtle

See the attached file where I have written an array formula to obtain the desired result. thanks.
second largest.xlsx

Regards,
DILIPandey

<click on below 'star' if this helps>  Register To Reply

3. ## Re: Return 2nd largest value (ignoring duplicates) with criteria

That only works for this specific example. I need something more automatic.

If you change the example to:

 A B 1 25 1 40 1 40 2 25 2 35 2 35 2 35 2 35 2 35  Register To Reply

4. ## Re: Return 2nd largest value (ignoring duplicates) with criteria

Sqwirtle,

Using the setup in dilipandey's workbook, this regular formula returns the correct result in both scenarios: ``Please Login or Register  to view this content.``  Register To Reply

5. ## Re: Return 2nd largest value (ignoring duplicates) with criteria

haha, I tried following that formula and it gave me a headache. Maybe I've been looking at this problem too long or I just have no idea what your formula does

If possible, can you give a quick explanation what it's basically doing? If that's not possible, it's all good.

Anyways it works! Thanks so much!

Edit: Although it works, I am getting a Circular Reference Warning. Any ideas ?  Register To Reply

6. ## Re: Return 2nd largest value (ignoring duplicates) with criteria

Here is a curveball, what if I wanted to do the same thing but instead of returning that 2nd largest number I wanted to return the corresponding number in column C

 A B C 1 25 101 1 40 102 1 40 103 2 25 104 2 35 105 2 35 106 2 35 107 2 35 108 2 35 109

I originally took Tigeravatar's formula and added another index in front of it with a match for the row which worked. The problem though is that I am still getting that underlying circular argument and it is bogging down the whole spreadsheet very badly.

Any suggestions?  Register To Reply

7. ## Re: Return 2nd largest value (ignoring duplicates) with criteria

That formula shouldn't cause a circular reference (unless you have the formula somewhere in A1:B9). I'd have to see an example workbook that is experiencing the problem.

As for what the formula does (warning, gets kind of long)...

ROW(INDIRECT("1:"&ROWS(B1:B9))))

This basically creates an array of sequential numbers, 1 through the number of rows in the data. In this case, the data is 9 rows, so it creates the following array:
{1,2,3,4,5,6,7,8,9}

That array is fed to the Large() function with the purpose of returning the numbers in descending order. In Excel terms:
Large(index_of_values,{1,2,3,4,5,6,7,8,9})

So it returns Large(_,1) then Large(_,2), etc to get all of the values in descending order.

The index_of_values is generated from this part:
INDEX((A1:A9=2)*B1:B9,)

Basically, that says if A1:A9=2, then return B1:B9. A1:A9=2 will return an array of True/False values. Anything True will return a 1 and anything False will return a 0. That gets multiplied to B1:B9, so anything that met the criteria will return successfully, and anything that was false will return a 0 (because it gets multiplied by 0). So now we have the values according to the criteria. The Large() function discussed earlier then sorts them in descending order so that the max value is first.

Next is the CountIf part, which is used to get the 2nd largest value:
COUNTIF(B1:B9,MAX(INDEX((A1:A9=2)*B1:B9,)))+1

You'll notice the use of the Index() with the use of (A1:A9=2)*B1:B9. This is the second time this formula we're using it. That's because this time, that array of values isn't being sorted, its being evaluated by Max(). That returns the largest value. The largest value is fed to the CountIf function, so now we know exactly how many times that max value occurs. This is useful because the Large() function earlier sorted the values in descending order. So if the max value is repeated 4 times, the first 4 values are the max value. So we add +1 to the count so that we return the next number in the series, which is the 2nd largest number ignoring duplicates. Now that we have which position it is, we can feed that to the overarching Index() formula:

Index(array_of_sorted_values, position_number)
The array_of_sorted_values is generated from the Large(Index(),Row()) function. The position_number is from the CountIf(value,Max(Index())+1) function. So now, out of that sorted list, it grabs the 2nd highest number.

Hopefully that explanation helps.  Register To Reply

8. ## Re: Return 2nd largest value (ignoring duplicates) with criteria Originally Posted by Sqwirtle Here is a curveball, what if I wanted to do the same thing but instead of returning that 2nd largest number I wanted to return the corresponding number in column C
Didn't see this post until after I was done writing up the explanation. Formula changes quite a bit, here you go: ``Please Login or Register  to view this content.`` Originally Posted by Sqwirtle The problem though is that I am still getting that underlying circular argument and it is bogging down the whole spreadsheet very badly
As mentioned in the explanation post, I'd need to see an example workbook that is experiencing the problem in order to diagnose it.  Register To Reply

9. ## Re: Return 2nd largest value (ignoring duplicates) with criteria

Thank you so much for all your help.

I really appreciate you taking the time to explain it as well.

My data set is ~1800 lines summarizing it into ~100 lines and its bogging this bad boy down like crazy. So once I had them all calculated, I had to copy special (values). I hope no else who looks at my spreadsheet needs to know where those numbers came from. -_-

Thanks again!  Register To Reply

10. ## Re: Return 2nd largest value (ignoring duplicates) with criteria

New quick method:

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save  Register To Reply

11. ## Re: Return 2nd largest value (ignoring duplicates) with criteria

Thanks tigeravatar for the explanation and formula. I plugged in the formula into my spreadsheet of 36000 rows and it seems like it doesn't work but does when I use a smaller set. Is this a problem due to the size of my sheet?  Register To Reply