Return text which appears in one list but not in a second list?

[chasfh]

I have a list of 12 teams and a second list of 11 teams, and I want to return as text the name of the 12th team not in the second list.

It's an unusual need, I know, but I did seem to find two sources that give the same solution:

Return text when appears in a list but not if in another list

I need some help with creating a formula that returns a text string from a column of data (that will include blanks) but it cant also be included in another..

techcommunity.microsoft.com

I should be getting the result "Redlegs" in cell G28, where the formula is, but instead I'm getting a #CALC! error. See screenshot below.

What am I missing here? Is this not even the right formula to use for what I need here?

 

[ISY]

Hi

=Unique(G3:G27,,1)

 

[awoohaw]

A slight update to the formula given by @ISY .
If your ranges will be moving around, you can use the VSTACK function to ensure the cell ranges with data are together:

Mr excel questions 67.xlsm
	G
3	Orioles
4	Cardinals
5	Beaneaters
6	Twins
7	White Sox
8	Tigers
9	Phillies
10	Redlegs
11	Yankees
12	Dodgers
13	Blues
14	Pirates
15
16	White Sox
17	Twins
18	Beaneaters
19	Orioles
20	Cardinals
21	Yankees
22	Tigers
23	Blues
24	Phillies
25	Pirates
26	Dodgers
27	Redlegs
chasfh

Cell Formulas
Range	Formula
G27	G27	=UNIQUE(VSTACK(G3:G14,G16:G26),,1)

 

[Peter_SSs]

What am I missing here?

To answer your question: You had the ranges the wrong way around in your formula.

23 10 16.xlsm
	F	G
1
2
3		Orioles
4		Cardinals
5		Beaneaters
6		Twins
7		White Sox
8		Tigers
9		Phillies
10		Redlegs
11		Yankees
12		Dodgers
13		Blues
14		Pirates
15
16
17		White Sox
18		Twins
19		Beaneaters
20		Orioles
21		Cardinals
22		Yankees
23		Tigers
24		Blues
25		Phillies
26		Pirates
27		Dodgers
28	Mine	Redlegs
29	Yours	#CALC!
Missing

Cell Formulas
Range	Formula
G28	G28	=FILTER(G3:G14,NOT(COUNTIF(G17:G27,G3:G14)))
G29	G29	=FILTER(G17:G27,NOT(COUNTIF(G3:G14,G17:G27)))