Compare two columns, return missing

[jcooooper]

Hi all,

In the below, I am trying to return the missing values in named_range_1, that appear in named_range_2.

Third column is the desired result.

I've spent a couple of hours with the UNIQUE, COUNTIF and FILTER options without much success : /

Any help appreciated!

Thanks!

named_range_1	named_range_2	Output
Argentina	Argentina	Austria
Australia	Australia	Bermuda
	Austria	Cyprus
Bahamas	Bahamas
Bahrain	Bahrain
Belgium	Belgium
	Bermuda
Brazil	Brazil
British Virgin Islands	British Virgin Islands
Bulgaria	Bulgaria
Cameroon	Cameroon
Canada	Canada
Cayman Islands	Cayman Islands
Chile	Chile
China	China
Colombia	Colombia
	Cyprus
Czech Republic	Czech Republic
Denmark	Denmark

 

[Fluff]

How about

+Fluff 1.xlsm
	A	B	C
1	named_range_1	named_range_2	Output
2	Argentina	Argentina	Austria
3	Australia	Australia	Bermuda
4		Austria	Cyprus
5	Bahamas	Bahamas
6	Bahrain	Bahrain
7	Belgium	Belgium
8		Bermuda
9	Brazil	Brazil
10	British Virgin Islands	British Virgin Islands
11	Bulgaria	Bulgaria
12	Cameroon	Cameroon
13	Canada	Canada
14	Cayman Islands	Cayman Islands
15	Chile	Chile
16	China	China
17	Colombia	Colombia
18		Cyprus
19	Czech Republic	Czech Republic
20	Denmark	Denmark
Lists

Cell Formulas
Range	Formula
C2:C4	C2	=FILTER(named_range_2,ISNA(MATCH(named_range_2,named_range_1,0)))
Dynamic array formulas.

Named Ranges
Name	Refers To	Cells
named_range_1	=Lists!$A$2:$A$20	C2
named_range_2	=Lists!$B$2:$B$20	C2

 

[Toadstool]

Hi JCooooper,

I only have Excel 2016 so this would be my approach, which assumes no duplicates in named_range_2

JCooooper.xlsx
	A	B	C
1	named_range_1	named_range_2	Output
2	Argentina	Argentina	Austria
3	Australia	Australia	Bermuda
4		Austria	Cyprus
5	Bahamas	Bahamas
6	Bahrain	Bahrain
7	Belgium	Belgium
8		Bermuda
9	Brazil	Brazil
10	British Virgin Islands	British Virgin Islands
11	Bulgaria	Bulgaria
12	Cameroon	Cameroon
13	Canada	Canada
14	Cayman Islands	Cayman Islands
15	Chile	Chile
16	China	China
17	Colombia	Colombia
18		Cyprus
19	Czech Republic	Czech Republic
20	Denmark	Denmark
Sheet1

Cell Formulas
Range	Formula
C2:C20	C2	=IFERROR(INDEX(named_range_2,AGGREGATE(15,6,ROW(named_range_2)-ROW($B$1)/((ISNA(MATCH(named_range_2,named_range_1,0)))),ROW()-ROW($C$1))),"")

Named Ranges
Name	Refers To	Cells
named_range_1	=Sheet1!$A$2:$A$20	C2:C20
named_range_2	=Sheet1!$B$2:$B$20	C2:C20

 

[jcooooper]

How about

+Fluff 1.xlsm
	A	B	C
1	named_range_1	named_range_2	Output
2	Argentina	Argentina	Austria
3	Australia	Australia	Bermuda
4		Austria	Cyprus
5	Bahamas	Bahamas
6	Bahrain	Bahrain
7	Belgium	Belgium
8		Bermuda
9	Brazil	Brazil
10	British Virgin Islands	British Virgin Islands
11	Bulgaria	Bulgaria
12	Cameroon	Cameroon
13	Canada	Canada
14	Cayman Islands	Cayman Islands
15	Chile	Chile
16	China	China
17	Colombia	Colombia
18		Cyprus
19	Czech Republic	Czech Republic
20	Denmark	Denmark
Lists

Cell Formulas
Range	Formula
C2:C4	C2	=FILTER(named_range_2,ISNA(MATCH(named_range_2,named_range_1,0)))
Dynamic array formulas.

Named Ranges
Name	Refers To	Cells
named_range_1	=Lists!$A$2:$A$20	C2
named_range_2	=Lists!$B$2:$B$20	C2

Thank you, works wonderfully!

 

[Fluff]

Glad we could help & thanks for the feedback.