List all related values, based on multiple criterias

[DarkoDeign2]

I have two worksheets, Addresses and Tracking.
In Tracking there are data in col C2:C1500, F2:F1500, U2:U1500, AF2:AF1500
In Addresses I have search criterias in cells: $C$2, $E$1 and $E$2. Based on the selected criterias I would to list the corresponding matches in cells: $E$5: $E$100 as shown below

Tracking sheet:

Book1
	C			F															U											AF
1	ID Number			Global Local															Supplier											Status
2	8501240			Global															Ultra Cars											CLOSED
3	8508186			Global															E-cigaretts											ONGOING
4	8508260			Global															Chengdu Bakeries											CLOSED
5	8508679			Local															Sushi on the beach											CLOSED
6	8509825			Local															Palonky Balonky											CLOSED
7	8511698			Global															Ultra Cars											ONGOING
8	8510942			Global															E-cigaretts											ONGOING
9	8513012			Local															Mommy's Burgers											CLOSED
10	8513832			Local															Palonky Balonky											CLOSED
11	8513902			Local															Mommy's Burgers											CLOSED
12	8514855			Local															Sushi on the beach											CLOSED
13	8515072			Local															Chengdu Bakeries											CLOSED
14	8515106			Global															Kevin's Bacon Shop											ONGOING
15	8516460			Global															E-cigaretts											CLOSED
Tracking

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:AF1500	Expression	=$AF2="Closed"	text	NO
A2:AF1500	Expression	=$AF2="Ongoing"	text	NO
A2:AG1500	Expression	=$AF2="SALES STOP"	text	NO
A2:AG1500	Expression	=$AF2="DC Block"	text	NO

Cells with Data Validation
Cell	Allow	Criteria
AF2:AF15	List	=Status

Addresses sheet:

Book1
	B	C	D	E	F
1				Global
2	Company name:	E-cigaretts		ONGOING	CLOSED
3				2	1
4
5				8508186	8516460
6				8510942
7
8
9
10
11
12
Addresses

Cell Formulas
Range	Formula
E3:F3	E3	=IF(COUNT(E5:E100)<>0, COUNT(E5:E100), "")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C2	Expression	=ISBLANK(C2)	text	NO

Cells with Data Validation
Cell	Allow	Criteria
E1	List	Global; Local

 

[Fluff]

How about

Fluff.xlsm
	B	C	D	E	F
1				Global
2	Company name:	E-cigaretts		ONGOING	CLOSED
3				2	1
4
5				8508186	8516460
6				8510942
7
8
9
10
11
12
Sheet6

Cell Formulas
Range	Formula
E3:F3	E3	=IF(COUNT(E5:E100)<>0, COUNT(E5:E100), "")
E5:E6,F5	E5	=FILTER(Tracking!$C$2:$C$1500,(Tracking!$F$2:$F$1500=$E$1)*(Tracking!$U$2:$U$1500=$C$2)*(Tracking!$AF$2:$AF$1500=E2))
Dynamic array formulas.

 

[DarkoDeign2]

Hi,

Thanks for the suggestion. When I copy the formula I get "Spill" and "Calc" errors in the cells E5 and below.

 

[Fluff]

Clear all cells below E5 & F5 that is causing the spill error.

 

[DarkoDeign2]

I got rid of the spill and Calc errors. But when C2 is empty it is showing values in E5 and F5...

Book1
	B	C	D	E	F
1				Global
2	Company name:			ONGOING	CLOSED
3				1	3
4
5				8598169	8516989
6					8519093
7					8596215
8
9
10
11
Addresses

Cell Formulas
Range	Formula
E3:F3	E3	=IF(COUNT(E5:E100)<>0, COUNT(E5:E100), "")
E5,F5:F7	E5	=IFERROR(FILTER(Tracking!$C$2:$C$1500,(Tracking!$F$2:$F$1500=$E$1)*(Tracking!$U$2:$U$1500=$C$2)*(Tracking!$AF$2:$AF$1500=E2)),"")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C2	Expression	=ISBLANK(C2)	text	NO

Cells with Data Validation
Cell	Allow	Criteria
E1	List	Global; Local

 

[Fluff]

What do you want it to do?

 

[DarkoDeign2]

If C2 is blank I want E5 and F5 to be blank as well

 

[Fluff]

In that case use

=IF(C2="","",FILTER(Tracking!$C$2:$C$1500,(Tracking!$F$2:$F$1500=$E$1)*(Tracking!$U$2:$U$1500=$C$2)*(Tracking!$AF$2:$AF$1500=E2),""))

 

[DarkoDeign2]

This works perfectly! Thanks for the quick help!

 

[Fluff]

You're welcome & thanks for the feedback.