Index matching with Large() calculation

Hi all,

First post here so please be gentle. Self taught excel guru, finding limits and googling my way past them

However, I haven't been able to find a solution to this one (or perhaps I am over complicating it) I know what I need to do but not quite sure how:

So I have a sheet called `Data`, which has multiple criteria in the following layout (its bigger than this - about 13k lines - but that gives you the idea):

A_____________B_________C
product code___category___sales
ABC123456_____Fruit______34
ABC432342_____Fruit______3
MEA123456_____Meat_____22
MEA432342_____Meat_____19

Currently, I use the following formula on another sheet, in column B, to list the top 20 selling products from the data sheet:

A B
1 =INDEX(Data!$1:$1048576,MATCH(LARGE(Data!$C:$C,$A1),Data!$C:$C,0),1)
2 =INDEX(Data!$1:$1048576,MATCH(LARGE(Data!$C:$C,$A2),Data!$C:$C,0),1)
3 =INDEX(Data!$1:$1048576,MATCH(LARGE(Data!$C:$C,$A3),Data!$C:$C,0),1)
4 =INDEX(Data!$1:$1048576,MATCH(LARGE(Data!$C:$C,$A4),Data!$C:$C,0),1)
etc

This works perfectly in giving me the overall top 20 - however - I want to now be able to produce a top 20 by product category (column B) on the data tab...

In my head the formula is the same as the above, but with another criteria applied to only search in the specified product category.

Any ideas?

Many thanks!

Hi and welcome to the forum.

Be sure that best way to describe your problem is to upload a sample workbook.

Be sure that all sensitive data removed, showing what data you are working with, a few examples of what your expected outcome is, and how you arrived at that.

To attach a small sample workbook.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

1 way could be this.

We create a unigue list of products.

=LOOKUP(REPT("Z";5);CHOOSE({1\2};"";INDEX(Data!$B$2:$B$20;MATCH(TRUE;INDEX(ISNA(MATCH(Data!$B$2:$B$20;I$1:I1;0));0);0);1)))

We get the quantities

=SUMIF(Data!$B$2:$B$20;I2;Data!$C$2:$C$20)

We rank these.

=IFERROR(RANK(J2;$J$2:$J$10);"")

W e stop here or we continue using.

=IFERROR(INDEX($I$2:$I$10;MATCH(LARGE($J$2:$J$10;ROW(A1));$J$2:$J$10;0));"")

And...

=IF(E2<>"";SUMIF($I$2:$I$10;E2;$J$2:$J$10);"")

What about these?

=IFERROR(INDEX(Data!$A$2:$A$6;MATCH(LARGE((Data!$C$2:$C$6+ROW(Data!$C$2:$C$6)/1000000)*(Data!$B$2:$B$6=$F$1);ROW(A1));(Data!$C$2:$C$6+ROW(Data!$C$2:$C$6)/1000000)*(Data!$B$2:$B$6=$F$1);0));"")

=IFERROR(INDEX(Data!$B$2:$B$20;SMALL(IF(Data!$A$2:$A$20=$F2;ROW(Data!$B$2:$B$20)-1);ROW(Data!$B$1)));"")

ARRAY formulas.

=IF(E2="Meat";SUMIF(Data!$A$2:$A$20;F2;Data!$C$2:$C$20);"")

Adding a validation list in F1 and using in all formulas F1 as reference cell, is better?

Well done!

Simply and without me want to in touch on .. I have a simple question.

Do not you ever thank anyone for any reason, or only in this case?

Thank you for the feed back and your explanations.

To say the true i have not understand what are you try to do using SUMPRODUCT.

Perhaps using COUNTIFS?...