Which version of Excel are you using?

Here's an "easy" way to see what is happening.

1) Construct a demonstration database for yourself, for example:

Column A, Column B
aa,1
bb,2
bb,3
dd,4
bb,5

2) In cell C1 put the number 2
3) In cell D1 enter this version of the formula (remembering the
Ctrl+Shift+Enter): =INDEX(B1:B5,SMALL(IF(A1:A5=C2,ROW(B1:B5)),C1))
4) From the menu: Tools Formula Auditing Evaluate Formula, then keep
clicking on "Evaluate" to see what is happening.

--
Regards
-
Andy Wiggins FCCA
www.BygSoftware.com
Excel, Access and VBA Consultancy


"Rodney" wrote in message
...

Something is amiss here?

| This is the solution Frank Kabel gave you last time around:
| try the array formula (entered with CTRL+SHIFT+ENTER):
| =INDEX(B1:B60000,SMALL(IF(A1:A60000="lookup_value" ,ROW(A1:A60000)),2))

Is someone able to offer the above statement as a story
so I can nut out how the query is expressed please?
I am assuming INDEX is (reference)

My Criteria range is B2:B61490
the value of each criteria lies in cells E2:E61490
So my Criteria array is B2:E61490 ?

The list to which I need the value of the 2nd occurence
shown in the array, lies in cells B61495:B61956

I could not get Mr. Kabels' formula to work.

Thanks for any suggestions.
Rodney