The IF expression returns an "array" of values

`=IF(A2:C6="apple",ROW(A2:C6)-ROW(A2)+1,ROW(C6)+1)`

For every cell in A2:C6 that contains "apple" the relative row number is returned, e,g, if Apple is in A2 then 1 is returned, if Apple is in A3 2 is returned etc. If "apple" isn't in any cell then the default value ROW(C6)+1 is returned, so, for example - if "Apple" is only in A3 and C6 you'd get this result returned by the above

`{7,7,7;2,7,7;7,7,7;7,7,7;7,7,5}`

You can see that by putting the formula in a cell then pressing F2 followed by F9

The FALSE part of the formula isn't normally required - this will suffice

`=IF(A2:C6="apple",ROW(A2:C6)-ROW(A2)+1)`

then FALSE values return FALSE.....which is ignored by SMALL anyway

...so a better formula would be something like this:

`=INDEX(B2:B6,SMALL(IF(A2:A6="apple",ROW(A2:A6)-ROW(A2)+1),2))`

That will give you the corresponding value in B2:B6 for the 2nd row with "Apple" in A2:A6 - there's no need to look at A2:C6 when the fruits are only within the first column - this is an "array formula" which needs to be confirmed with `CTRL+SHIFT+ENTER`

`,2)`

shouldn't be there. The post in the forum you linked has`SMALL()`

which takes the value of`,2)`

but which isn't needed in this`IF()`

statement. Your`IF()`

won't work anyway like this because`A2:C6="apple"`

cannot be evaluated as is. – Jerry May 14 '13 at 19:06`ROW(A2:C6) = 2`

is false because this was meant as an array formula, and`ROW(A2:C6)`

would return the Row of the cell that returns true to being equal to`apple`

. The`-ROW(A2)+1`

is to account for the headers a column might have. so that that the array will NOT return the Row of the worksheet, but how many rows Down from`A2`

the match is. And then when no matches it will simply return a default value, most likely c7 is a blank cell, but they use it as C6+1 for drag down and readability. – user2140261 May 14 '13 at 19:13