This tutorial will help you to highlight the nth occurrence of a value in a column, in a row, in each row, or in a cell range in Google Sheets.

To highlight the first, second, third, fourth, or nth occurrence of a value in Google Sheets, I am going to follow cell address matching.

I’ve already used the same method in a few of my earlier tutorials. Here are the post links of some of them.

• Skip Duplicates in Min | Small Value Highlighting Row Wise in Google Sheets.
• Highlight Max Value Leaving Duplicates in Row Wise in Google Sheets.
• Highlighting Multiple Groups and Control Tick Boxes in Google Sheets.

You may check these tutorials when you have free time as it has no connection with this tutorial. Coming back to our tutorial, let me explain cell address matching in conditional formatting.

Match Cell Address Instead of Value in Conditional Formatting

Assume a certain range contains values and you want to match a value to highlight in that range.

In that case, first, find and extract the cell address of the value you want to match in the range. Then in conditional formatting match that extracted cell address in the cell addresses range.

The above is the method that I am going to use to highlight the nth occurrence of a value in Google Sheets.

We can very easily match a value and extract the cell address of it from a range using the ADDRESS function in Google Sheets. That tips you can find below.

Highlighting, or we can say ‘Conditional format’ (Format menu) to apply a fill color to the nth occurrence of value is simple if we want to apply it in a single column, single row, or each row.

If you want the same to a cell-range (multi-column array) then things are slightly complicated. Don’t worry! I am trying to simplify the things for you.

You can find 4 formula rules in this tutorial. They are for a single column, single row, each row and for a cell range.

Highlight Nth Occurrence in a Column

I have the values to highlight entered in B2:B and the occurrence number is in cell C2.

The below example shows the changes in highlighting when I change the occurrence number in cell C2.

The value to highlight is “apple” which is in cell D2. If you wish you can change that value also from “apple” to “orange” or to any other value.

Here is the conditional format formula for the same range.

Formula Rule 1:

=cell(
     "address",B2
)
=index(
     filter(
        address(row($B$2:$B),column($B$2:$B)),
        $B$2:$B=$D$2
     ),
     $C$2
)

To apply this formatting select B2:B. Then go to Format>Conditional formatting. There follow the below format settings.

• Apply to range: B2:B
• Format rules: Custom formula is (please refer to the below image).
• Insert the above formula.

Formula (Custom Rule) Explanation

There are two parts (for explanation purpose) in the formula.

Part 1:

=cell(
     "address",B2
)

Part 2:

=index(
     filter(
        address(row($B$2:$B),column($B$2:$B)),
        $B$2:$B=$D$2
     ),
     $C$2
)

The first part returns the cell address of B2 whereas the second part returns the cell address of the nth (cell C2) occurrence of the value “apple” (cell D2).

If part 1 output equals part 2 output in any row (as cell address B2 is relative in part 1 so it would change to B3, B4, B5, and so on automatically), such cells would be highlighted.

In part 2 the cell references are absolute. So it won’t change.

In the above formula which highlights the nth occurrence of a value in Google Sheets, I know, you may have doubts about the second part of the formula. It works like this (step by step).

Step 1

The below Address formula returns the cell IDs of the range B2:B.

=ArrayFormula(address(row($B$2:$B),column($B$2:$B)))

Note: You may not see the ArrayFormula in my formula rule as it’s only required in standalone use.

Step 2

The Filter function filters the above formula returned cell IDs matching the value (“apple”) in cell D2.

=filter(
     address(row($B$2:$B),column($B$2:$B)),
     $B$2:$B=$D$2
)

Step 3

The Index here extracts the nth matching cell ID from the filtered output.

=index(
     filter(
        address(row($B$2:$B),column($B$2:$B)),
        $B$2:$B=$D$2
     ),
     $C$2
)

You May Like: Select Only the Required Column from an Array Result in Google Sheets.

Highlight Nth Occurrence in a Row and as Well as in Each Row

Let’s now see how to highlight nth occurrence in a row in Google Sheets.

The value to highlight is in B2:2 (‘Apply to range’ in conditional formatting), the occurrence number is in cell A6 and the value (criterion) is in cell A8.

Formula Rule 2:

=cell(
     "address",B2
)
=index(
     filter(
        address(row($B$2),column($B$2:2)),
        $B$2:2=$A$8
     ),
     $A$6
)

Since I have already explained the formula for a column (Formula Rule 1), here I am not explaining the formularule 2 in detail.

Here the logic is also the same. In concise;

• The Address function returns the cell addresses of the range B2:2 (row 2).
• The Filter filters the cell IDs matching the criterion in cell A8 (“apple”).
• The Index returns the cell ID of the nth occurrence.

What about the above Same Type of Highlighting in Each Row?

By making minimal changes to the formula rule 2, you can highlight the nth occurrence of a value in each row in Google Sheets.

Here are those changes.

First of all the ‘Apply to range’ must be B2:4 as per the above range or B2:1000 to cover a wider range.

Formula Rule 3:

=cell(
     "address",B2
)
=index(
     filter(address(row(),column($B2:2)),
     $B2:2=$A$8
     ),
     $A$6
)

The changes are within the Address formula.

Highlight Nth Occurrence in a Cell Range

Scenario:

There are values in columns A and B (there can be more columns).

The nth occurrence here is based on the values in row-wise. Don’t confuse it with the earlier example (formula rule 3).

I hope the below image will help you understand the scenario better.

In this, the formula highlighted the 6th occurrence of the value “apple” in cell B7. The arrow marks indicate the direction of counting of occurrence.

If you consider the value “apple”, the occurrences are in the cells B2, C2, C4, B6, C6, B7, and C7 that in the order 1, 2, 3, 4, 5, 6, and 7 respectively.

So the 6th occurrence (cell D2) of the value “apple” is in the cell B7.

To highlight nth occurrence in a cell range as above, we can use a formula similar to the one used in our first example (formula rule 1).

Here additionally you need to flatten the references within the Filter function. You can find/read more about Flatten here – How to Use the FLATTEN function in Google Sheets.

Formula Rule 4:

=cell(
     "address",B2
)
=index(
     filter(
        flatten(address(row($B$2:$B),column($B$2:$C))),
        flatten($B$2:$C)=$E$2
     ),
     $D$2
)

That’s all. Enjoy!