## Identify missing three character alpha code numbers

*Article updated on February 24, 2018*

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:**

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:

=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)))

### Download 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

**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 row number 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

### One Response to “Identify missing three character alpha code numbers”

### Leave a Reply

**How to add a formula to your comment:**

<code>your formula</code>

Remember to convert less than and larger than signs to html character entities before you post your comment.

**How to add VBA code to your comment:**

[vb 1="vbnet" language=","]

VBA code

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org

Add picture link to comment.

**Contact Oscar**

You can contact me through this webpage

Why you use List1 and List2?

Alternative (array formula) :

=SMALL(IF(COUNTIF($A$2:$B$16, ROW($65:$90))=0, ROW($65:$90), ""),ROW(A1))