There are 26*26*26 = 17,576 different alpha codes beginning with AAA and ending with ZZZ. Column F contains all 17,576 alpha codes.

The formula in cell D3 compares the values in column B with the values in column F and extracts only those who are missing in column B.

Array formula in cell D3:

Explaining array formula in cell D3

Step 1 - Identify missing values

The COUNTIF function counts values based on a condition or criteria, in this case, it counts values in $F$3:$F$17578 based on $B$3:$B$11.

A 0 (zero) indicates a missing value.

COUNTIF($B$3:$B$11, $F$3:$F$17578)=0

becomes

{0;1;0;1;1;0;0;0;0;0;0;0 ... 0}=0

and returns

{TRUE; FALSE; TRUE; FALSE; FALSE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE}

Step 2 - Replace TRUE with corresponding row number

The IF function returns the corresponding row number if boolean value is TRUE. FALSE returns "" (nothing).

IF(COUNTIF($B$3:$B$11, $F$3:$F$17578)=0, MATCH(ROW($F$3:$F$17578), ROW($F$3:$F$17578)), "")

becomes

IF({TRUE; FALSE; TRUE; FALSE; FALSE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE}, MATCH(ROW($F$3:$F$17578), ROW($F$3:$F$17578)), "")

and returns

{"";2;"";4;5;"";"";"";"";"";"" ... ""}

Step 3 - Extract row number

The SMALL function returns the k-th smallest number in a cell range or array. SMALL(array, k)

SMALL(IF(COUNTIF($B$3:$B$11, $F$3:$F$17578)=0, MATCH(ROW($F$3:$F$17578), ROW($F$3:$F$17578)), ""), ROWS($A$1:A1))

becomes

SMALL({"";2;"";4;5;"";"";"";"";"";"" ... ""}, ROWS($A$1:A1))

The ROWS function returns the number of rows in a cell reference, in this case the cell reference expands as the cell is copied to cells below.

SMALL({"";2;"";4;5;"";"";"";"";"";"" ... ""}, ROWS($A$1:A1))

becomes

SMALL({"";2;"";4;5;"";"";"";"";"";"" ... ""}, 1)

and returns 2.

Step 4 - Return value

The INDEX function returns a value based on a row and column number. The column number is not needed here.

INDEX($F$3:$F$17578, SMALL(IF(COUNTIF($B$3:$B$11, $F$3:$F$17578)=0, MATCH(ROW($F$3:$F$17578), ROW($F$3:$F$17578)), ""), ROWS($A$1:A1)))

becomes

INDEX($F$3:$F$17578, 2)

and returns "AAB" in cell D3.

Get excel *.xlsm file

I made all the alpha codes using a UDF, you don't need the UDF to extract missing alpha codes so you can disable macros for this workbook if you like.

Three character alpha code.xlsm

---

Weekly Blog EMAIL

[newsletter_signup_form id=1]

Welcome! I am Oscar and here to help you out.

Feel free to comment and ask Excel questions.

Make sure you subscribe to my newsletter so you don't miss new blog articles.

Related articles

---

Extract numbers from a column

I this article I will show you how to get numerical values from a cell range manually and using an […]

Count unique distinct values in two columns

Formula in C12: =SUM(1/COUNTIF($B$3:$B$8, $B$3:$B$8))+SUM(IF(COUNTIF($B$3:$B$8, $D$3:$D$8)=0, 1/COUNTIF($D$3:$D$8, $D$3:$D$8), 0)) How to create an array formula Double press with left mouse […]

Identify missing numbers in a column

The image above shows an array formula in cell D6 that extracts missing numbers i cell range B3:B7, the lower […]

How to highlight duplicate values

Table of Contents How to highlight duplicate values Highlight the smallest duplicate number 1. How to highlight duplicate values The […]

Extract unique distinct records based on a criterion

In a previous article "Automatically filter unique row records from multiple columns", I presented a solution to filter out unique […]

Select cell A1 on all sheets before you close a workbook [VBA]

This post demonstrates a macro that automatically selects cell A1 on each sheet right before you close a workbook. The […]