As you may know, we can use the Google Sheets Index function to offset rows. In this tutorial, let’s learn how to index filtered range in Google Sheets. That means, using Index, how to offset rows in a filtered range to the correct cells.

The interesting thing is that the index row offset of the filter formula result will be based on the count of criterion.

I’m finding it really tough to explain what does the topic means. No worries! I’ll explain it with one example. Before that here are the functions involved in index filtered range formula in Google Sheets.

The functions that we are going to use in the formula are Index, Filter, and Countif. In addition to this, to make the Filter formula fruitful, I am also including Regexmatch.

Let’s understand the above combo and how to index filtered range in Google Sheets (based on count). I hope a few of you may find this combo useful in your real-life use.

Index Filtered Range in Google Sheets – Topic Explained

The topic is all about offsetting the filtered output to the correct spot in a criteria range.

Here I am just using a simple list to explain the topic. Once learned the purpose, you can use a table (multi-column range) with the formula. That also I’ll try to include in this post.

Here is the example.

The list to filter is in A2:A6 which contains the name of some of the fruits, i.e. “Orange” and “Apple” with U.S. Fruit grades. Further, the range C2:C7, which is the criterion range, contains some fruit names without grades.

So we have two range (arrays) containing fruit names – one with fruit grades (filter range) and the other without fruit grades (filter criterion).

Let’s consider the fruit “Orange” which is in the cells C2, C3, and C7 and leave the fruit “Apple” for the time being. On the image, you may please just concentrate on the formula result in corresponding cells D2, D3, and D7.

I hope you can understand what’s happening behind.

My index filtered range formula in those cells (D2, D3, and D7) in Google Sheets, take the criteria from the same row (C2, C3, and C7) to filter A2:A6 and offset the result based on the criteria count.

The formula offsets the filtered fruits with grades as follows – offset 1 row in D2, 2 rows in D3, and 3 rows in D7. So even if the values in C2, C3, and C7 are the same, the formula returns different results.

The same is the case with the other criterion “Apple”. Let’s go to the formula to index the filtered range as above (based on count) in Google Sheets.

Index, Filter, and Countif Combo in Google Sheets

I hope you have taken your own time to understand the topic. If you are OK to proceed, then follow my below step-by-step instructions to write the Index, Filter, and Countif combination.

Sample Data and Criteria

First, open a Google Sheets file and enter the values in columns A and C as per the screenshot above. So you have completed step 1.

Now the next step is writing the index filtered range formula. We can start with Filter so that we can use Index to offset the rows.

Filter Formula (Regexmatch Included)

The first criterion is in cell C2. Key the below formula in cell D2.

=filter(
     $A$2:$A$6,
     regexmatch($A$2:$A$6,C2)
)

The formula filters $A$2:$A$6 for the rows in this range partially matches the criterion in cell C2. The Regexmatch in Filter is for a partial match.

In the formula, I’ve kept the filter range as an absolute reference and the criterion cell reference as a relative reference. It’s possible with the proper use of $ signs.

Related: Placement and Use of Single or Double Dollar Symbols in Formula in Spreadsheet.

The above helps us to keep the filter range the same and to just change the criterion to C3, C4… when dragging the formula down.

Index Filter Formula Result Based on Count of Criterion

In this third step, we can index the above-filtered range in Google Sheets.

The Index function lets us offset rows and columns. Here we are just using the row offset and leaves the column offset in Index.

See the Index function syntax first to understand the positioning (order) of the said two offsets.

INDEX(reference, [row], [column])

When we leave the column offset, the formula would return all the columns in the ‘reference’. The ‘reference’ here is the above filter formula result.

We are only concerned about the ‘row’ offset, right?

Countif as Index Offset Row

Before further proceeding, you should try the below Countif formula in any cell, for example in cell B2 and drag-down as we are using it as the row offset in Index.

=countif($C$2:C2,C2)

The formula returns the running count of fruits.

That means, for the item “Orange”, we will use 1 as the row offset in Index in cell D2, 2 in cell D3, and 3 in cell D7.

The above Counitfs are just for explanation purposes. So you can remove the above Countif formulas.

Formula to Index Filtered Range Based on Count in Google Sheets

Now I am going to combine the Index, Filter and Countif as per the generic formula below.

=index(
     filter_formula,
     countif_formula
)

So the formula in cell D2 will be as below.

=index(
     filter($A$2:$A$6,regexmatch($A$2:$A$6,C2)),
     countif($C$2:C2,C2)
)

The formula has been copy-pasted in D3:D7.

In Cell D6 the formula returns #REF! error because the fruit “Apple” repeated the third time in column C. But in the filter, there will be only two rows (“Apple U.S. No. 1” and “Apple U.S. Fancy”). So Index can’t offset 3 rows.

To avoid errors and exclude blank cells, if any, in C2:C7 we can further modify the formula as below.

=if(
     len(C2),
     iferror(
        index(
           filter($A$2:$A$6,regexmatch($A$2:$A$6,C2)),
           countif($C$2:C2,C2)
        )
     ),
)

This way we can index the filtered range in Google Sheets.

Tips (Formula in a Two Column List)

I have a two columns list that contains an extra column B with quantity. What changes should I make to the formula?

Change the filter range $A$2:$A$6 to $A$2:$B$6 as highlighted above or if you just want the quantity, change the filter range to $B$2:$B$6. There are no changes in the Index or Countif part.

That’s all. Enjoy!