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:

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

Related posts:

1. Identify missing numbers in a range in excel
2. Count matching cell values in two columns in excel
3. How to filter numbers inside (and outside) number ranges in excel
4. Count unique values in two lists combined in excel
5. How to calculate missing months in a given date range in excel
6. Identify largest text value in a column using array formula in excel
7. Extract numbers and text from a range using array formula in excel
8. Create a list of all numbers or text in a column in excel
9. Merge two columns with possible blank cells in excel (formula)
10. Identify numbers in sum using solver in excel