Index function reference error

[mdr8c]

I'm trying to search an array for a specific value and then return all instances in the array.

=index(Sheet1!C48:D77,SMALL(IF(Sheet1!J48:J77="Gold",ROW(Sheet1!J48:J77)),ROW(1:1)),1)

If I use the following function it returns a value correctly.

SMALL(IF(Sheet1!J48:J77="Gold",ROW(Sheet1!J48:J77)),ROW(1:1))

However, when I embedded it into the index function it returns a reference error.

What do I have wrong, should I be using a different function?

 

[Jonmo1]

The row Function
ROW(Sheet1!J48:J77)
Is returning numbers from 48 to 77 (the row #s in the range)

The Row# in an INDEX function is not actually a Row # persay.
It's an index #, the nth cell in the referenced range.

So Index(A1:A10,3) = A3 (A3 is the 3rd cell in A1:A10)
BUT
Index(A5:A15,3) = A7 (A7 is the 3rd cell in A5:A15)

So if the smallest Row# that meets the criteria is 50,
Then the Index is trying to get the 50th cell in C48:C77
But there are only 30 cells in that range
So it is a #REF! Error


You can change the formula to
=index(Sheet1!C:C,SMALL(IF(Sheet1!J48:J77="Gold",ROW(Sheet1!J48:J77)),ROW(1:1)))