In one of the earlier posts, I extracted the first, last, min and max occurence from an array for a particular lookup. Refer this post.
The next step perhaps would be to answer if it is possible to extract the second, third occurences and so on. Consequently to extract all occurences for a particular lookup_value.
Consider the same example, as earlier:
Each of the 4 persons randomly appear for a test and their scores are recorded. I had given methods to extract the scores for 1st and last attempt by each of the 4 perons. Now lets find out their scores for 2nd, 3rd, etc attempts
The table above gives the results. Row 1 gives the number of attempt.
The formula used for getting to the result is
F2=IF(COUNTIF(Person,$E2)>=F$1,INDIRECT(ADDRESS(SUMPRODUCT(LARGE((Person=$E2)*ROW($B$2:$B$15),COUNTIF(Person,$E2)+1-F$1)),3)),”-“)
Person is B2:B15
As you would see, I have used sumproduct to avoid using array formula and a combination of indirect and address to extract results.
Here are the steps to understand this formula –
- Lets begin with the component LARGE((Person=$E2)*ROW($B$2:$B$15),COUNTIF(Person,$E2)+1-F$1
- Remember Large function has the syntax LARGE(array,k) which returns the kth largest value from the array. For example if k=2, then the function returns second largest value in the array.
- Now, (Person=$E2)*ROW($B$2:$B$15) would create an array like {0,2,0,0,5,0,0,0,9,0,0,0,0,0,0} which is essentially the row numbers of cells in the array where Person=Mike is true
- For the k part in large function, I have entered COUNTIF(Person,$E2)+1-F$1. Mike occurs 3 times in array Person, so the k would be 3,2,1, such that 1st attempt score is followed by 2nd attempt score and so on
- Once this is clear, rest is easy
- Address function has syntax Address(row_num, column_num, [abs_num]). row_num is given from the large function, column_num is entered as 3, which is the column number of Score, [abs_num] is omitted which returns address in absolute eg $C$2
- Indirect(“$C$2”) returns the value inC2.
- If condition at the beginning simply evaluates if we have exhausted all occurences of Mike. Once done, it returns a “-“.
§ Note: This also is a better way for multiple extraction because sorting of original table is not required.
There are 3 other alternative formulas given in the earlier post to achieve the same result.
Else, if one method is sufficient for you, use this and have fun 🙂
Results_array through the formula
E2 = A2
Useful Excel Tips 