UtterAccess HomeUtterAccess Wiki

Welcome Guest ( Log In | Register )

Custom Search
Edit Discussion
> First Last Nth Occurance    
First Last Nth Occurance

Match does a good job finding the first row on which a value is found. Sometimes it is helpful to be able to get subsequent rows for the same value.

The three formulas for this process are:

Find first Occurrence =MATCH(Value,RangeLookup,0)

Find Nth Occurrence: =LARGE(((RangeLookup=Value)*ROW(RangeLookup)),COUNTIF(RangeLookup,Value)-Occurance+1)

Find Last Occurrence: =MAX((RangeLookup=Value)*ROW(RangeLookup))

Note: The Nth Occurance and Last Occurence formulas must be entered as array formulas.

The attached spreadsheet shows an example of these formulas at work. In it we are looking for the first, 3rd and last occurrence of the days of the week in a 31 day period. Media: First_Last_Nth_Occurance.zip

Edit Discussion
Custom Search
Thank you for your support!
This page has been accessed 1,576 times.  This page was last modified 15:19, 21 February 2014 by dflak.   Disclaimers