Here’s a handy Google Sheets tip—how to assign the same sequential number to duplicate values in a list using an array formula. This works great with grouped or categorized data.

For example, if the item “Cherry” first appears in row 3, all its duplicates should get the number 3—not 4, 5, etc.

This tutorial explains how to generate the same sequential number for duplicate values using an array formula that works whether your list is sorted or unsorted.

Assigning Sequential Numbers to Duplicates – Single Column Example

Let’s begin with a simple example.

In column B, you have a list of items—some repeated. We want to assign sequential numbers in column A so that all duplicates share the same number as their first appearance.

The Array Formula

Place the following formula in cell A2 and ensure the column below it is blank:

=ArrayFormula(IF(LEN(B2:B), XMATCH(B2:B, UNIQUE(B2:B)), ))

This formula assigns the same sequential number to each unique item. All “apple” entries get 1, “orange” gets 2, and so on.

How the Formula Works

Let’s break it down:

1. UNIQUE(B2:B) returns the first instance of each unique item:

apple  
orange  
cherry  
mango  
banana  

2. XMATCH(B2:B, UNIQUE(B2:B)) checks the position of each item in this list:

• All “apple” → 1
• All “orange” → 2
• And so on.

3. The outer IF(LEN(...)) ensures the formula only runs for non-blank rows, helping it auto-expand without errors.

Tip: If you’re working with a fixed list, say B2:B12, you can simplify the formula to:

=ArrayFormula(XMATCH(B2:B12, UNIQUE(B2:B12)))

Why Not Use the Classic IF Formula?

You might be familiar with this common approach:

1. Type 1 in cell A2
2. In A3, enter:
   =IF(B2=B3, A2, A2+1)
3. Drag it down the column.

This assigns the same number to duplicate items—but only if the list is sorted. It compares each item with the one below and increments the number only when the value changes.

However, if the list is unsorted or new duplicates are added later, the numbering breaks. For example, if you add “apple” again in row 13, it won’t get the same number as earlier “apple” entries.

That’s why the array formula approach is far more robust and dynamic.

Assigning Same Sequential Numbers to Multi-Column Duplicates in Google Sheets

Now, what if duplicates are based on two columns, such as item name (B2:B) and grade (C2:C)? In that case, we need to check both columns together.

Multi-Column Array Formula

=ArrayFormula(IF(LEN(B2:B), XMATCH(B2:B & C2:C, UNIQUE(B2:B & C2:C)), ))

This formula combines columns B and C into one string (B2:B&C2:C) and finds the position of each unique combination. All identical combinations get the same number.

Conclusion

You’ve now learned how to:

• Assign the same sequential number to duplicate entries
• Handle both single-column and multi-column duplicates
• Use array formulas that auto-expand and work on unsorted lists

Additional Resources

• Skip Hidden Rows in Sequential Numbering in Google Sheets
• How to Create Backward Sequence Numbering in Google Sheets
• Sequence Numbering in Merged Cells In Google Sheets
• Adding N Blank Rows to SEQUENCE Results in Google Sheets
• Create a Dynamic Fibonacci Sequence in Google Sheets
• How to Number Rows as 1, 1.1, 1.2, 1.3 in Google Sheets