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

[Execu2v]

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]

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.

Book2
	A	B	C	D	E	F	G
1	Cust Name	Type	Var		Search	Type	Var <=
2	Name1	A	10		Name	A	15
3	Name2	B	20		Name1
4	Name3	C	30		Name9
5	Name4	D	40		Name13
6	Name5	A	20
7	Name6	B	20
8	Name7	C	30
9	Name8	D	40
10	Name9	A	10
11	Name10	B	20
12	Name11	C	30
13	Name12	D	40
14	Name13	A	10
15	Name14	B	20
Sheet1

Cell Formulas
Range	Formula
E3:E5	E3	=FILTER($A$2:$A$15,($B$2:$B$15=$F$2)*($C$2:$C$15<=$G$2),"No Match")
Dynamic array formulas.

 

[Execu2v]

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]

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.