Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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.

share|improve this question

migrated from stackoverflow.com May 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
2  
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
1  
@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
show 5 more comments

1 Answer

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

share|improve this answer
    
+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
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.