Sorry, I don't think Formula Auditing is available in 97. Perhaps you will
need to sell your body so you can afford to upgrade :-)

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


"Rodney" wrote in message
...
Ah! Great Andy, thank you. (I have Excel97)
I have just mortgaged the house,
and purchased "Mastering Excel" 4th ed Thomas Chester, Richard Alden.

I am beginning to understand the obtuse ways that ROW and
INDEX work, by using small 12 cell examples.

Your suggestion is warmly welcomed,
and I look forward to the journey.

Best Regards
Rodney




| 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
|
|
|
|
|
|
|
|