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:
Lookupvalue: Specified value to look up.
Lookuprange: Range to look for value in.
Iteration: Duplicate index to return (i.e. Nth iteration)
Example data, starting in A1:
Apple
Banana
Orange
Apple
Grapefruit
Apple
Example functions:
=ITERATION("Apple",A1:A6,2)
Would return Range("A4"), with value of Apple.
=ITERATION("Apple",A1:A6,4,TRUE)
Would return #N/A, as there is only 3 values found and Exact_iteration was passed as TRUE.
=ITERATION("Apple",A1:A6,4)
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#/++. :)
3 comments
-
Harlan Grove commented
More reasons just to copy Google Sheets's FILTER function.
INDEX(FILTER(...),instance#)
-
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.