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.

Please Login or Register  to view this content.

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:

Please Login or Register  to view this content.

This doesn't work:

Please Login or Register  to view this content.

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.

Please Login or Register  to view this content.

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?