comparing 3 ranges and return values that are missing based on 2 criteria

[flashgordie]

Hello,

Further to my post on July 27-2022 (After comparing 2 ranges, return values that are missing based on criteria)

I am hoping for some help on expanding that effort. I would like to add a second criterial (I believe it would be an “and” to capture only the items that have a value in column G > 0.However, when I try to add it, I get an error.

Looking for a formula(s) to start at A20:B?? based on the "#" , that will list the items that appear in D6:E28, that do not appear in A3:B9, as well as that have a category = "Colors" AND have a Value >0

I tried these two ideas, but I feel I’m missing the obvious…. something like… =FILTER(D3:E28,(and(g3:g28>0,(F3:F28="colors"))*(ISNA(MATCH(D3:D28,A3:A9,0))))) or =FILTER(D3:E28,(g3:g28>0,(F3:F28="colors")*(ISNA(MATCH(D3:D28,A3:A9,0)))))

Any help again would be greatly appreciated.

formula for missing items in two lists.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	Workbook 1 list			Workbook 2 list					This works….
2	#	Desc		#	Desc	Category	Value		where I4 =FILTER(D3:E28,(F3:F28="colors")*(ISNA(MATCH(D3:D28,A3:A9,0))))
3	1212	Red		1011	White	Colors	0
4	1314	Violet		2801	Donkey	Animals	0		1011	White
5	1415	orange		1212	Red	Colors	0		1819	Purple
6	1516	Yellow		5551	Bike	Vehicles	0		1014	Black
7	1617	Green		2809	Zebra	Animals	0
8	1718	Blue		1516	Yellow	Colors	0
9				5552	Train	Vehicles	0		This needs helps…..
10				5557	Tractor	Vehicles	0
11				2802	Elephant	Animals	0		Where I17 would include an "and" to capture only items with values >0
12				1718	Blue	Colors	0		something like… =FILTER(D3:E28,(and(g3:g28>0,(F3:F28="colors"))*(ISNA(MATCH(D3:D28,A3:A9,0)))))
13				5556	Bus	Vehicles	0		or =FILTER(D3:E28,(g3:g28>0,(F3:F28="colors")*(ISNA(MATCH(D3:D28,A3:A9,0)))))
14				1617	Green	Colors	0
15				1819	Purple	Colors	1		Should return…
16				2803	Pig	Animals	0
17				2805	Cow	Animals	0		1819	Purple
18				2804	Sheep	Animals	0		1014	Black
19				5553	Truck	Vehicles	0
20				2806	Dog	Animals	0
21				1014	Black	Colors	1
22				5554	Motorcycle	Vehicles	0
23				2808	Horse	Animals	0
24				2807	Cat	Animals	0
25				5555	Car	Vehicles	0
26				2810	Goat	Animals	0
27				1415	orange	Colors	0
28				1314	Violet	Colors	0
29
Sheet1

Cell Formulas
Range	Formula
I4:J6	I4	=FILTER(D3:E28,(F3:F28="colors")*(ISNA(MATCH(D3:D28,A3:A9,0))))
Dynamic array formulas.

 

[Fluff]

How about

=FILTER(D3:E28,(F3:F28="colors")*(ISNA(MATCH(D3:D28,A3:A9,0)))*(G3:G28>0))

 

[flashgordie]

@Fluff .... Again, that look very promising. Thank you so much. You assistance is truly appreciated.

 

[Fluff]

Glad to help & thanks for the feedback.