Give us a proper ITERATION or INSTANCE function for duplicates
In many cases we need to find the Nth number of a duplicate. There are funky workarounds with complicated formulas, and currently no simple way to do this. I would like to propose a function to return the Nth instance of a value found in a specified range. It would return a range, like the INDEX function does now, which would make it extremely powerful and simplify many existing formulas.
I don't care about the name, so the best I could think of was ITERATION or INSTANCE, but I'm sure someone could find other clever names as well.
I imagine this would be a function with simple parameters:
Lookup_value: Specified value to look up.
Lookup_range: Range to look for value in.
Iteration: Duplicate index to return (i.e. Nth iteration)
[Exact_iteration]: Boolean. Optional. Specifies whether the closest iteration should be returned or an exact match.
Example data, starting in A1:
Would return Range("A4"), with value of Apple.
Would return #N/A, as there is only 3 values found and [Exact_iteration] was passed as TRUE.
Would return Range("A6"), as [Exact_iteration] wasn't specified, so FALSE would be the default resulting in the max iteration (being 3 in this case).
I'd be willing to bet this could be made to be fairly efficient written in C#/++. :)
Harlan Grove commented
More reasons just to copy Google Sheets's FILTER function.
Zack Barresse commented
Charles Williams has something similar in FastExcel, titled AVLOOKUPNTH. This is similar to what I'm describing, but more like LOOKUPNTH.
Bob Greenblatt commented
Allow wild card characters in the lookup_value.