The first part of the formula: ROWS($A$1:$A1)>COUNTIF(Main!$C$2:$C$28,"Mike") checks to see if the row number you're in is greater than the number of "Mike" entries...if it is greater then a blank is inserted (so no errors are visible).
The next part: INDEX(Main!$B$2:$B$28,SMALL(IF(Main!$C$2:$C$28="Mike",ROW(Main!$C$2:$C$28)-ROW(Main!$C$2)+1),ROWS($A$1:$A1))))
is like a vlookup for multiple matches... It uses the Index() function which requires you to index the table or column to extract from and it requires the Row number to extract from which is gotten from this part: SMALL(IF(Main!$C$2:$C$28="Mike",ROW(Main!$C$2:$C$28)-ROW(Main!$C$2)+1),ROWS($A$1:$A1)
The Small() function just allows us to step up and extract one match at a time, starting from the first match found. It will extract only if a "Mike" is found in range C2:C28 and then it will return the corresponding row number within the range. The -ROW(Main!$C$2)+1 is added for robustness (incase you insert rows above, then the result won't skew).
The last ROWS($A$1:$A1) is the k factor for the Small() function, which is like a step factor in a For...next loop in VBA.
The CSE confirmation is because it is an array formula and you have to confirm with those keys to make these formulas work.
Then you copy down... The only thing that changes copying down is the ROWS($A$1:$A1) in the 2 occurances within the formula.. again to determine the step or row number we're in.
The only thing that changes when going across the table is what is Indexed (i.e. INDEX(Main!$B$2:$B$28..) and the specific item to use as a criteria (i.e. "Mike").
Hope this clarifies a bit.
LinkBack URL
About LinkBacks
Register To Reply
Originally Posted by chris21
Bookmarks