This blog article answers a comment in this blog article: Identify missing values in two columns using excel formula

Question: I need to do exactly the same thing, but with three character alpha codes instead of numbers. Can anyone help?

Answer:

Identify-missing-three-character-alpha-code-numbers

This array formula in C2 finds missing numbers between 65 and 90:

=SMALL(IF(COUNTIF(List1, ROW($65:$90))+COUNTIF(List2, ROW($65:$90))=0, ROW($65:$90)), ROWS($C$1:C1)) + CTRL + SHIFT + ENTER

copied down as far as necessary.

To find missing numbers between 97 and 122 you can use this array formula in C2 :

=SMALL(IF(COUNTIF(List1, ROW($97:$122))+COUNTIF(List2, ROW($97:$122))=0, ROW($97:$122)), ROWS($C$1:C1)) + CTRL + SHIFT + ENTER

copied down as far as necessary.

Named ranges
List1 (A2:A16)
List2 (B2:A15)
What is named ranges?

How to customize the formula to your excel spreadsheet
Change the named ranges. If your list starts at, for example, F2. Change $C$1:C1  in the above formula to F1:$F$1.

Download excel sample file for this tutorial.
Identify missing three character alpha code numbers in excel.xls
(Excel 2007 Workbook *.xlsx)

Functions in this article:

COUNTIF(range,criteria)
Counts the number of cells within a range that meet the given condition

IF(logical_test;[value_if:true];[value_if_false])
Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE

ROW(reference) returns the rownumber of a reference

SMALL(array,k) Returns the k-th smallest row number in this data set.

ROWS(array) returns the number of rows in a reference or an array