Arrays in the Large function

[charlie1105]

Hi all

Short story - I'm trying to restrict the array used in the large function, so that it only considers certain rows (based on the value in another column). So if Column A contains my "Category" and Col B contains my values, I want to use large to return the kth item where the category = "some specific value"

If someone could help me with this step, I think I can take it from here - anyone who's interested, this is what my end goal is..

I'm building a dashboard, so that you can select the category from a drop down list (in cell), you'll then get a list generated with the top ten items. I'm pretty sure I can work out the index match to get the item name (col C) from the value (there are no duplicate values) which is returned by large, just stuck on restricting the array in large to only consider those values of a given category!

Cheers

Charlie

 

[Domenic]

To return the largest value, assuming that D2 contains the category of interest, try...

=LARGE(IF(A2:A100=D2,B2:B100),1)

...confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

xl-central.com

 

[charlie1105]

Thanks for that - just what I'm looking for - so obvious now, I need to brush up on my array formulas!