# How can you evaluate this excel expression?

I am trying evaluate the following expression in Excel:

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

If `apple` is contained within the array, the if condition would return

``````ROW(A2:C6)-ROW(A2)+1
``````

and

``````ROW(A2:C6) = 2
``````

so

``````ROW(A2:C6)-ROW(A2)+1 = 2-2+1 = 1
``````

If the condition evaluates to False, it would return

``````ROW(C6)+1 = 6+1 = 7
``````

I am trying to get the formulas from this site to work.

-

## migrated from stackoverflow.comMay 16 '13 at 1:18

This question came from our site for professional and enthusiast programmers.

So what's the problem? – Melanie May 14 '13 at 19:06
The `,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
@Jerry - A2:C6="apple" is fine - it returns an "array" as per my answer – barry houdini May 14 '13 at 19:14
@MarcelloRomani - yes COUNTIF is better if you just want to count "apple"s in a range.....but the point here is not to count them but to return an array which contains the rows where the "apple"s reside - and that in turn allows you to get the 1st, 2nd, 3rd occurrence etc. and extract values from another column that correspond to those – barry houdini May 14 '13 at 19:21

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`

-
+1 Also, the F2+F9 is a cool feature! – Jerry May 14 '13 at 19:18
@user2371290 your comment appears to be incomplete can you clarify?? – barry houdini May 15 '13 at 0:11
How does the program account for the fact that it returns the quantity for the row when apple occurs the second time ? If I use =IF(A2:C6="apple", "Bob", "Peter") , shouldn`t the formula return Bob ? – user2371290 May 15 '13 at 1:10