Index( Match( Small( If

Hello, First Time Posting. Here is an example of my data:
excel error.png

I have a list of Products with their matching Annual Sales, & % sold as Our Brand. I would like this data sorted by % Our Brand, then Sales $ on a different page that will be formatted to send to a PDF.

For example:

Pears $10,000 99%
Apples $1,999 100%
Toasters $2,000 100%

becomes

Toasters 100% $2,000
Apples 100% $1,999
Pears 99% $10,000

I need my template to be repeatable many times over so taking the data and manually sorting (which works) is not ideal.

In the attached snapshot Column:

W: product name (sorted by highest sales amount)
X: Sales Amount (sorted high to low)
y: vlookup from sales dollar to the correct % amount
z: LARGE formula used to sort the % amount high to low
AA: where the below formula was used to order sales dollars based on %
AB: the correct product name

The formula I have utilized is: =INDEX($X$1:$X$61,SMALL(IF($Y$1:$Y$61=Z1,ROW($X$1:$X$61)),ROW(1:1)),1)

which works through the "100%" 's but then fails to 'reset' the row from row(9:9) to row (1:1) when the % changes to 98%.

I tried inserting a if statement like this: =INDEX($X$1:$X$61,SMALL(IF($Y$1:$Y$61=Z1,ROW($X$1:$X$61)),if(z8=z7,ROW(8:8),row(1:1))),1)

which does not work, as the row(1:1) continues to increase to match the first row(8:8) essentially nullifying it.

Is there a way for the Array to recognize that the % amount has changed and that the row reference needs to reset to (1:1) so that it can accurately pull the sales amount?

In the example you can see how column AA, shows Sales Totals (using the first formula) with AB showing product name (an index/match to get the correct Product Name), but at Row 10 there are errors as the 100% becomes 98%.

If I manually adjust the formulas last Row from row(10:10) to (1:1) it then shows the correct highest Sales amount for 98%. (which would be Localized Temp Therapy, $240K).

Thoughts/examples/answers much appreciated.

If more info is needed please advise first time poster.

Travis

Rather than attaching pictures of your data, attach the Excel workbook instead.

Pete