Top 10 Index - Match Multiple Criteria

[MSW_80]

I try to create a top 10 list of products that have a coverage over a year ranked by inventory value. Using index match to map against the inventory value i ran into 2 problems.

Here's the formula i use (in that case to return the item no):

=INDEX(ITEM;MATCH(LARGE(IF((COVERAGE> 365)(COVERAGE< 9999)(CATEGORY="Bike");INVENTORY;);COUNTA(G$64:G64));INVENTORY;0))

The large formula returns the nth inventory value for products where coverage exceeds 365 days, is less than 9999 (default value in case we don't have a sales projection) and falls into category "Bike".

That works so far as intended, however in case the inventory value isn't unique, looking up the row via the match function gives me the first entry, not the one that's in line with the large if criteria (so it can return a product from another category).

On top if there are less than 10 items to begin with, the large if returns a value of 0 and the index match then fills in the remaining rows out of the Top 10 with the first entries that report 0 value regardless of the if criteria.

[AliGW]

Willkommen!

Will you please attach a sample Excel workbook? We are not able to work with or manipulate a picture of one and nobody wants to have to recreate your data from scratch.

1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired results are also shown (mock up the results manually).

3. Make sure that all confidential data is removed or replaced with dummy data first (e.g. names, addresses, E-mails, etc.).

4. Try to avoid using merged cells as they cause lots of problems.

Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

Please pay particular attention to point 2 (above): without an idea of your intended outcomes, it is often very difficult to offer appropriate advice.

[MSW_80]

Edit: Original post now includes sample data.

[jason.b75]

Assuming item numbers are unique, try this method.

In E4

=IFERROR(AGGREGATE(14,6,(COVERAGE >365)/(COVERAGE < 9999)/(CATEGORY="65 - Bike Carriers")*INVENTORY,ROWS(E$4:E4)),"")

Apply conditional formatting to the cells with the above formula,

Rule:- =E4 < 0.01
Format:- #

This is done to blank out any zero, or almost zero results. One row in the sample was producing inventory of something like 0.000063 due to hidden decimals in sheet 1. (edit:- Check Sheet 1, cell I732)

In A4

=IF(ROUND(E4,2),INDEX(ITEM,AGGREGATE(14,6,(COVERAGE > 365)/(COVERAGE < 9999)/(CATEGORY="65 - Bike Carriers")/(INVENTORY=E4)*ROW(ITEM),COUNTIF(E$4:E4,E4))),"")

In B4

=IF(A4="","",INDEX(NAME,MATCH(A4,ITEM,0)))

Use the last formula for the remaining columns, changing the named range as necessary.

Edited copy of your sample attached with changes made.

[MSW_80]

That's perfect - thank you very much!