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?



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:

Counts the number of cells within a range that meet the given condition

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