how to return the second occurence with index function ?

**masterinex** · 06-23-2013, 01:04 AM

hello,

I am trying to return the quantity for the second occurrence of apple from a table using

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

the description says that it returns the quantity for the second occurrence of apple,

but if i look at it this way...

ROW(A2:C6) = 2

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

Then

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

=

IF(A2:C6="apple",1, 7)

and since apple is contained within A2:C6

IF(A2:C6="apple",1, 7) = 1

so

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

=

SMALL(1,2)

= 1

Then

INDEX(A2:C6,1 ,2) would return the value from the first row and the second column, but the quantity for the second occurrence of apple should be in row 4 and column 2 .

**dilipandey** · 06-23-2013, 01:16 AM

HI MasterInex,

Instead on image, upload an excel workbook and mention you expected output. Thanks.

Regards,
DILIPandey
<click on below * if this helps>

**Ghozi Alkatiri** · 06-23-2013, 04:37 AM

This may be simpler than yours
(array formula)
=INDEX(B2:B6;SMALL(IF(A2:A6="apple";C2:C6);2))

**Tony Valko** · 06-23-2013, 10:05 AM

Try it like this...

Array entered**:

=INDEX(B:B,SMALL(IF(A2:A6="apple",ROW(A2:A6)),2))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.