Author: Oscar Cronquist Article last updated on November 29, 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?

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

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.