I appreciate it when someone asks questions. I think it's better if they gain an understanding, so that they can do more on their own later. Here's a brief rundown of how it works:
First of all, an array function (the Control-Shift-Enter part) tells a function to handle a range of values instead of 1 value. For example, if you have a formula
=A1+1
where A1=1, then the result will be 2. If you turn it into an array formula by putting a range instead of a single cell and confirm with CSE, like this:
=A1:A3+1
where A1=1, A2=2, A3=3, then the result will be {2,3,4} in an array. It's possible to show the whole array on a sheet by entering the formula in a range of cells, but for this formula we just use the array internally.
Back to the formula. This part:
IF($B$2:$B$10="x",ROW($B$2:$B$10))
translates to
IF(B2="x",ROW(B2)) which evaluates to 2 in our example
IF(B3="x",ROW(B3)) evaluates to 3
IF(B4="x",ROW(B4)) evaluates to FALSE
IF(B5="x",ROW(B5)) evaluates to 5
etc.
so the array from the IF ends up as
{2,3,FALSE,5,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE}
now we need to extract the row numbers from the array. The ROWS($C$2:$C2) part now comes into play. The ROWS function tells us how many rows are in the range. So ROWS($C$2:$C2) will equal 1. But as we copy that piece down the column, since the second 2 does not have a $ on it, it will act as a relative reference and change to ROWS($C$2:$C3), which will evaluate to 2. So that piece just converts to 1, 2, 3, 4, etc. as we go down the column.
So now we have
=SMALL({2,3,FALSE,5,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE}, 1)
where the 1 increments as we go down the column. So in row 2 we get row 2 from the array, in row 3 we get 3 (second smallest) from the array, in row 4 we get 5 from the array (third smallest), and for all other rows we get an error (SMALL ignores non-numeric values).
Then finally we take the 2, 3, 5 from that step and put it in the INDEX function to get the item from column A. The IFERROR piece is used to ignore the potential error from the last step. And the IF(C1="","" part at the beginning is to tell the function not to calculate at all if the row above it is empty. This is to improve the overall performance of the sheet by not doing lots of array formulas that aren't necessary.
And as to your questions, yes it will work on larger ranges. Just change the ranges in the formula to match your sheet. And it will work on separate sheets, just include the sheet reference, like so:
=IF(C1="","",IFERROR(INDEX(Sheet1!A:A,SMALL(IF(Sheet2!$B$2:$B$10="x",ROW($B$2:$B$10)),ROWS($C$2:$C2))),""))
Hope that helps!
