I am trying to find nth occurrence of a text entry in an array

The AGGREGATE function I have used in Excel works beautifully but when I try the same in Calc, it does not work.

The post at

says this is because Calc handles the DIV#0 error differently than Excel

That posting is marked as outdated, so I am hoping a solution has been discovered since then.

However,in troubleshooting with a new worksheet, I tried using a helper column - which I CANNOT do for my actual application because I have over 1,000 columns and 100 rows with reference to another worksheet with the same layout - and it handled the DIV#0 error just fine when the error was in the array being referenced rather than embedded in the AGGREGATE function. Excel handles the DIV#0 error in either scenario.

For the simple, trouble-shooting spreadsheet I have

Names = array of names (C2:C41 if it matters)

Search = the text I am searching for ((H2)

Nth_Find= the Nth occurrence number N

```
Row_Number_Of_Nth_Occurrence=AGGREGATE(15,6,ROW(Names)/(Names=Search)-ROW($C$2)+1,Nth_Find
```

Works great in Excel

Errors out in Calc

When I put the “ROW(Names)/(Names=Search)-ROW($C$2)+1” in a helper column and use it as the reference in the above formula it worked.

But as said above, I cannot use Helper columns in this instance.

I want to use a Function-based solution, NOT an Array-Function-based solution since the Array-Function solution is too resource intensive for use in thousands of cells in a worksheet.

I can get this to work with a convoluted SUMPRODUCT formula but if possible, would like a simpler AGGREGATE formula with fewer calculations.

```
=SUMPRODUCT(SMALL((Names=Search)*(ROW(Names)-ROW(C2)+1),Nth_Find+COUNTIF(Names,"<>"&Search)))
```

Any suggestions for how to use the AGGREGATE function in Calc to find the Nth occurrence of a text item in an Array?

Using Sumproduct or Aggregate with Index to find nth occurance.xlsx