Return nth Largest value using multiple criteria

**Jbentley** · 02-03-2011, 06:16 PM

Hi,
I've a two column dataset with one column for person name and one column for sales.
I'm trying return the nth largest value where the person name equals the value in a referenced cell.

My dataset may grow or shrink over time so I'm trying to avoid array formulae

Basically I'm looking for something like the below that actually works.

=LARGE((--($A$2:$A5000=$E$1)*($B$2:$B$5000)),2)

Sounds like it could be simple, but I'm bamboozelled

**davesexcel** · 02-03-2011, 08:28 PM

Try this,
D1 being the criteria to find in ColumnA and the value is on Column B
=SUMPRODUCT(MAX((A1:A21=D1)*(B1:B21)))

**Jbentley** · 02-03-2011, 08:57 PM

Brilliant!! Thanks Dave

I was after a way to find the 2nd, 3rd or 40th largest value. Substituting the MAX for LARGE and adding the 'nth' critieria looks to have done the job.

Much appreciated.

**Jbentley** · 02-03-2011, 10:03 PM

Not quite as sorted as I'd first thought.

I seem to have hit some sort of range / array limit while using LARGE.
At 12529 elements in each array the formula return #N/A
At 12528 elements in each array the fomula returns the correct value

The values in range A2:A12530 are all Strings and the values in range B2:B12530 are all numbers
This works:

=SUMPRODUCT(LARGE((OtherSheet!A2:A12529=ThisSheet!A3)*(OtherSheetB2:B12529),1))

This doesn't work:

=SUMPRODUCT(LARGE((OtherSheet!A2:A12530=ThisSheet!A3)*(OtherSheetB2:B12530),1))

Any clues anyone?

**Colin Legg** · 02-04-2011, 06:30 AM

Hi,

You haven't hit a limit that I know of and, once I corrected the typo in your formula, it worked fine for me in XL 2003.

=SUMPRODUCT(LARGE((OtherSheet!A2:A12530=ThisSheet!A3)*(OtherSheet!B2:B12530),1))

Either there's a bug in XL 2007 (which I can't check) or you should scrutinise the data in cells OtherSheet!A12530 and OtherSheet!B12530 (they don't happen to have a #N/A error?). You could check if it's the data in the worksheet that is causing the problem: if you clear the data in OtherSheet does the formula still return an error?