Need to nest a function in complex formula.

[Execu2v]

Thank you for looking in ...

I am creating four ranked customer lists, by segment, from a master sales analysis data set.

The desired lists show top, ranked sales variance for each segment, ABCD. ABCD segments are defined by two factors, 1) total sales and 2) sales variance.

In some cases, the wrong segment name results from the formula (below) because the variance amount ($812) is the same for unique customers in segments C and D. I assume it's using the first reference it sees.

My want is to nest a function -- an IFs or AND??? -- that assigns the right customer's name/#, per the variance ($812), using the assigned segment ID field from the data set. Assume the Segment ID exists in field AE.

How and where would I alter the formula below to query that the segment name must be "D" in addition to the ($812) Variance.

Big thanks to any/all who might post a solution!

=@IF(DT8="","",INDEX($AG$2:$AG$1541,AGGREGATE(15,6,(ROW($AD$2:$AD$1541)ROW($AD$2)+1)/($AD$2:$AD$1541=DU8),COUNTIF($DU$8:DU8,DU8))))

 

[hajiali]

can you post using XL2BB the cells you are working with and what your looking for as a solution

 

[Execu2v]

hajiali, thank you for taking time to review. The data posting protocol was as daunting as the original problem, however searched Google and used =UNIQUE(FILTER($H$2:$H$3042,($DV$5=$AF$2:$AF$3042) * (DU8=$AE$2:$AE$3042))). It allowed me to use multiple criteria while also listing multiple entities that shared criteria. ... The new SPILL error was another hurdle to over come! Again, I appreciate your want to help others. Impressive.

 

[Peter_SSs]

The data posting protocol was as daunting as the original problem

Most other users of the forum who try it seem to manage.
At what point of the instructions do you get stuck and what are the symptoms of the problem you hit?