After comparing 2 ranges, return values that are missing based on criteria

[flashgordie]

Any help would be appreciated. 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 AND that have a category = "Colors"

test.xlsx
	A	B	C	D	E	F
1	Workbook 1 list			Workbook 2 list
2	#	Desc		#	Desc	Category
3	1212	Red		1011	White	Colors
4	1314	Violet		2801	Donkey	Animals
5	1415	orange		1212	Red	Colors
6	1516	Yellow		5551	Bike	Vehicles
7	1617	Green		2809	Zebra	Animals
8	1718	Blue		1516	Yellow	Colors
9	1819	Purple		5552	Train	Vehicles
10				5557	Tractor	Vehicles
11				2802	Elephant	Animals
12				1718	Blue	Colors
13				5556	Bus	Vehicles
14				1617	Green	Colors
15				1819	Purple	Colors
16				2803	Pig	Animals
17				2805	Cow	Animals
18				2804	Sheep	Animals
19				5553	Truck	Vehicles
20	1011	White		2806	Dog	Animals
21	1014	Black		1014	Black	Colors
22				5554	Motorcycle	Vehicles
23				2808	Horse	Animals
24				2807	Cat	Animals
25				5555	Car	Vehicles
26				2810	Goat	Animals
27				1415	orange	Colors
28				1314	Violet	Colors
Sheet1

 

[Fluff]

How about

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

 

[flashgordie]

How about

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

ohhhh. that looks promising!!!! Very promising. Thanks. I will see if I can build from that! I have know idea what its doing, but that's part of the fun Thanks again.

 

[Fluff]

You're welcome & thanks for the feedback.

 

[flashgordie]

I might Add to this solution, that once the formula is entered, 2 further things may need to happen. 1) you may need to select a range of cell below, to allow the formula to "spill" and find all those in the critera, and two, as this is an array, you also need to press ctrl+shft+enter to active the formula.

Fluff would you agree?

 

[Fluff]

as this is an array, you also need to press ctrl+shft+enter to active the formula.

Definitely not. You do not need to use Ctrl Shift Enter with xl365 & you should never do it on a formula that spills.

 

[alansidman]

An alternative is to use Power Query. Bring both tables into the PQ editor. Filter the second table for Colors. Join the two tables with an Anti Join. This will be very effective if you have a very large data set. Here is the Mcode for the anti join

let
    Source = Table.NestedJoin(Table2, {"#"}, Table1, {"#"}, "Table1", JoinKind.LeftAnti),
    #"Removed Columns" = Table.RemoveColumns(Source,{"Category", "Table1"})
in
    #"Removed Columns"