# Way over my skies with indexing, aggregates and multiple criteria!



## Execu2v (Dec 28, 2022)

PROBLEM: I am attempting to make customer lists and can't figure out how to include multiple customers that meet the required 3-part criteria. The goal:  "simple" lists of cust names ranked by revenue variance. I have the Rank, and $Varience calculations done, but stumped from there. -- A newbie with indexing and aggregate functions, my formula attempts thus far only show the first listing that meets the $Variance critera (not always the right CustType). Is there a multi-critera formula (for field 3 below) that will 1) key to the variance amount, 2) filter/require the right customer type, AND 3) show multiple CustNames that share same segment AND variance amount? 

The Goal: Lists by ranked sales variance for customers by Type IDs.
CustType    |     Rank    |    $Variance   |    CustName  

(Simplified) The source data fields:
A:A  Customer Name
B:B  Customer Type (four segments, A, B, C and D)
C:C  Sales Variance.

Really struggling here. Any/all genius help appreciated!


----------



## AhoyNC (Dec 28, 2022)

It would help if you would post a small sample and the expected result.
You may want to look at the FILTER function. Something like below.

Book2ABCDEFG1Cust NameTypeVarSearchTypeVar <=2Name1A10NameA153Name2B20Name14Name3C30Name95Name4D40Name136Name5A207Name6B208Name7C309Name8D4010Name9A1011Name10B2012Name11C3013Name12D4014Name13A1015Name14B20Sheet1Cell FormulasRangeFormulaE3:E5E3=FILTER($A$2:$A$15,($B$2:$B$15=$F$2)*($C$2:$C$15<=$G$2),"No Match")Dynamic array formulas.


----------



## Execu2v (Jan 2, 2023)

AhoyNC, thank you for taking time to review and for the recommendation. The data posting protocol was as daunting as the original problem, however your Filter suggestion helped direct me to a workable end. I 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 seeing multiple entities that shared criterial. ... The new SPILL error was another hurdle to over come? Again, I appreciate your want to help others. Impressive.


----------



## Peter_SSs (Jan 2, 2023)

Duplicate to: Need to nest a function in complex formula.

In future, please do not post the same question multiple times. Per Forum Rules (#12), posts of a duplicate nature will be locked or deleted.

In relation to your question here, I have closed this thread.


----------

