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. Identify missing values in two columns using excel formula
3. Identify missing values in a column using excel formula
4. Lookup between two lists of data to highlight missing data using conditional formatting in excel
5. Find missing numbers in a range from multiple columns
6. Merge two columns into one list in excel
7. Merge two lists
8. Extract largest values from two columns using array formula in excel
9. Merge three columns into one list in excel
10. Comparing two columns and sum unique values using array formula in excel