Oh, now I see the entire formula:
=IF(ISERROR(INDEX($A$1:$B$7,SMALL(IF($A$1:$A$7=$A$10,ROW($A$1:$A$7)),ROW(1:1)),2)),"",INDEX($A$1:$B$7,SMALL(IF($A$1:$A$7=$A$10,ROW($A$1:$A$7)),ROW(1:1)),2))
Where most people have problems with these types of formulas is in this
portion:
ROW($A$1:$A$7)
ROW() is simply a means of generating an array of numbers from 1 to n that
correspond to the total number of elements in the Index function. In the
formula, the Indexed range, B1:B7, holds a total of 7 elements. So,
ROW(A1:A7) generates an array from 1:7.
Here's where people get this messed up:
Assume the Indexed range is B11:B17. That still contains a total of 7
elements so we still need the ROW function to generate an array from 1 to 7.
A lot of people would use this: ROW(B11:B17). However, that would generate
an array from 11:17 and since the Indexed array is from 1:7 the formula
crashes.
The best way to prevent this problem is to subtract the offset of the range
then add 1:
ROW(B11:B17)-ROW(B11)+1
Now that generates the array we need: 1:7
I would write that formula as:
=IF(ROWS($1:1)<=COUNTIF(A$1:A$7,A$10),INDEX(B$1:B$7,SMALL(IF(A$1:A$7=A$10,ROW(A$1:A$7)-ROW(A$1)+1),ROWS($1:1))),"")
Biff
