Excel Formula to Index Duplicate Values Excluding Blank Cells

Managing duplicate entries in Excel while dodging empty cells is a tedious bottleneck for data analysts. While standard indexing sources like native VLOOKUP or basic filtering handle clean tables well, messy datasets require a more robust architecture.

Fortunately, deploying a specialized nested array formula grants users the power to extract only populated, repeating values seamlessly. The only stipulation is that legacy Excel versions require Ctrl+Shift+Enter execution. This method is highly effective for processing messy client lists, a technique widely used in corporate CRM audits.

Below, we examine the exact syntax and step-by-step implementation of this formula.

Managing data in Microsoft Excel often requires identifying and extracting specific patterns. One of the most common yet challenging tasks data analysts face is extracting a clean list of duplicate entries from a dataset while completely ignoring blank cells.

When your dataset is littered with empty rows, standard lookup and indexing formulas can break down. They may return annoying 0s, throw #N/A errors, or mistakenly treat blank cells as duplicate values.

In this comprehensive guide, we will explore how to build robust Excel formulas to index duplicate values while strictly excluding blank cells. Whether you are using the modern, dynamic array-enabled Excel (Microsoft 365 / Excel 2021) or an older legacy version of Excel (Excel 2019, 2016, or 2013), we have got you covered with step-by-step solutions.

Understanding the Logic: The Dual Challenge

Before diving into the formulas, it is important to understand the two problems we need to solve simultaneously:

• Identifying Duplicates: A value is considered a duplicate if it appears more than once in our target range. In Excel formula terms, this means COUNTIF(Range, Value) > 1.
• Excluding Blank Cells: Blank cells must not be evaluated. If we have multiple blank cells, a simple duplicate check might treat those blanks as duplicate entries and index them. We must explicitly instruct Excel to only evaluate cells where Value <> "".

Depending on your version of Excel, the approach to solving this dual challenge will differ significantly. Let's look at the modern, dynamic method first, followed by the classic array formula approach.

Method 1: The Modern Excel Way (Microsoft 365 & Excel 2021)

If you are using Microsoft 365 or Excel 2021, you have access to dynamic arrays. This makes the task incredibly straightforward, elegant, and fast. We will combine three powerful functions: FILTER, UNIQUE, and COUNTIF.

The Formula

Assuming your source data is in the range A2:A15, enter the following formula in your target cell (e.g., C2):

=UNIQUE(FILTER(A2:A15, (COUNTIF(A2:A15, A2:A15) > 1) * (A2:A15 <> "")))

How It Works Step-by-Step

This formula uses boolean logic to filter the dataset before extracting unique duplicates:

1. COUNTIF(A2:A15, A2:A15) > 1: This evaluates every cell in the range. It returns an array of TRUE or FALSE values. If a value appears more than once, it returns TRUE.
2. (A2:A15 <> ""): This checks every cell to see if it is not empty. If a cell contains data, it returns TRUE; if it is blank, it returns FALSE.
3. The Multiplication Operator (*): In Excel array formulas, multiplication acts as the AND logic gate. TRUE * TRUE equals 1 (Keep), while any calculation involving FALSE equals 0 (Discard). This ensures that only cells that are both duplicates and not blank are passed forward.
4. FILTER(...): This function takes the original range A2:A15 and extracts only the elements that met our criteria in step 3.
5. UNIQUE(...): The filter step returns every instance of the duplicates (e.g., if "John" is duplicated three times, "John" will appear three times in the filtered list). Wrapping it in UNIQUE ensures each duplicate value is listed only once.

Method 2: The Legacy Excel Way (Excel 2019 and Older)

If you are working in an older version of Excel, you do not have access to the FILTER or UNIQUE functions. Instead, you must rely on a traditional CSE (Ctrl + Shift + Enter) array formula. This formula uses a combination of INDEX, MATCH, IF, COUNTIF, and SMALL.

The Formula

Assuming your source data is in A2:A15, enter the following formula in your target cell (e.g., C2) and press Ctrl + Shift + Enter instead of just Enter. This will wrap the formula in curly braces { }.

=IFERROR(INDEX($A$2:$A$15, SMALL(IF(($A$2:$A$15 <> "") * (COUNTIF($A$2:$A$15, $A$2:$A$15) > 1) * (ISNA(MATCH($A$2:$A$15, $C$1:C1, 0))), ROW($A$2:$A$15) - ROW($A$2) + 1, ""), 1)), "")

Note: Drag this formula down to fill the columns below C2.

How It Works Step-by-Step

This legacy formula relies on complex row extraction logic to build a dynamic list:

• Excluding Duplicates Already Indexed: The ISNA(MATCH($A$2:$A$15, $C$1:C1, 0)) part prevents the formula from repeating values it has already found and listed above its current cell. As you drag the formula down, the expanded range $C$1:C1 grows to include previously indexed items.
• Validating Criteria: The statement ($A$2:$A$15 <> "") * (COUNTIF($A$2:$A$15, $A$2:$A$15) > 1) checks that the cell is not blank and is a duplicate.
• Extracting Row Numbers: If all conditions are met, ROW($A$2:$A$15) - ROW($A$2) + 1 generates a relative list of row indexes (e.g., 1, 2, 3... up to 14). If the conditions are not met, it returns an empty string "".
• SMALL(..., 1): This extracts the smallest valid relative row index from our array. By indexing only values that haven't been matched yet (using the MATCH criteria), the first unmatched duplicate row index is pulled.
• INDEX & IFERROR: INDEX fetches the value from A2:A15 using the row number provided by SMALL. If there are no more duplicates left to display, the formula throws an error, which IFERROR catches and cleanly converts into a blank space ("").

A Practical Walkthrough

Let's look at a practical example. Suppose we have a list of office supplies in column A, with several blank cells and some repeating entries. We want to compile a clean, non-repetitive list of duplicates in Column C.

In this dataset:

• Pen appears twice (Rows 2 and 6) - Duplicate (Keep)
• Pencil appears once (Row 3) - Unique (Discard)
• Blanks appear twice (Rows 4 and 7) - Blank (Discard)
• Notebook appears twice (Rows 5 and 9) - Duplicate (Keep)
• Eraser and Ruler appear once - Unique (Discard)

Applying either of our formulas in Column C will instantly yield Pen and Notebook, while omitting the blank rows entirely and avoiding any formula errors.

Pro-Tip: Sorting Your Output

If you are using Microsoft 365, you can easily sort the returned duplicates alphabetically. This is incredibly helpful when dealing with large datasets containing hundreds of duplicates. Simply wrap your dynamic array formula in the SORT function:

=SORT(UNIQUE(FILTER(A2:A15, (COUNTIF(A2:A15, A2:A15) > 1) * (A2:A15 <> ""))))

This ensures your extracted index is clean, structured, and instantly readable without requiring manual sorting or copy-pasting.

Summary of Solutions

Choosing the right formula depends on your current version of Excel:

• Excel 365 / 2021: Use =UNIQUE(FILTER(A2:A15, (COUNTIF(A2:A15, A2:A15)>1)*(A2:A15<>""))). It is faster, shorter, and automatically spills down without needing to copy the formula manually.
• Excel 2019 & Older: Use the nested INDEX, MATCH, SMALL, IF, COUNTIF array formula. Remember to press Ctrl + Shift + Enter and drag the formula down to cover your output range.

By using these targeted formulas, you can automate your data cleansing workflows, save hours of manual searching, and ensure your reporting dashboards remain completely free of blank rows and redundant entries.