## Identify missing three character alpha code numbers in excel

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:

copied down as far as necessary.

To find missing numbers between 97 and 122 you can use this array formula in C2 **:**

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

### Category: Missing values

Identify missing values in a column using excel formula

Table of contents Missing numbers (array formula) Missing numbers (vba) Missing numbers (array formula) Question: I want to find missing […]Comments(25) Filed in category: Excel, Missing values

Identify missing numbers in a range in excel

Question: How do I find missing numbers between 1-9 in a range? 1 3 4 5 6 7 8 8 […]Comments(5) Filed in category: Excel, Missing values

What values are missing in List 1 that exists i List 2?

Question: How to filter out data from List 1 that is missing in list 2? Answer: This formula is useful […]Comments(4) Filed in category: Compare, Excel, Missing values

Identify missing values in two columns using excel formula

Question: I want to find missing numbers in two ranges combined? They are not adjacent. Answer: Array formula in cell […]Comments(4) Filed in category: Excel, Missing values

Insert rows for missing values

HughMark asks: I have 2 columns named customer (A1) and OR No. (B1). Under customer are names enumerated below them. […]Comments(3) Filed in category: Excel, Missing values

Find missing dates in a set of date ranges

John S asks: I would like to find the dates MM/DD/YYYY missing in a set of date ranges. I haven't […]Comments(3) Filed in category: Dates, Missing values

How to calculate missing months in a given date range

Question: I have dates in a list. I would like to know how to identify missing months in this list […]Comments(2) Filed in category: Dates, Excel, Missing values

Find missing numbers in a range from multiple columns

EDIT: See this blog post on a shorter easier formula: Identify missing numbers in a range in excel. Question: I […]Comments(1) Filed in category: Excel, Missing values

Lookup between two lists of data to highlight missing data using conditional formatting in excel

To the right is a picture of two lists. How do I highlight values in List 1 that are not […]Comments(0) Filed in category: Conditional formatting, Excel, Missing values

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

### Leave a Reply

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

<code>your formula</code>

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