Let me clean that up a little bit (can you tell I am new at this):

A B C
1:........#67886537............................... .........
2:...............................Holdings.......Co upon....
3:.............USTN...........41750...........4.88 ......
4:.............AGCY...........14000..........5.366 .....
5:.............CORP...........27500..........5.337 ....

So, the current formula for holdings would be:

INDEX(A1:C5,SMALL(IF(A1:C5="USTN ",ROW(A1:C5)),ROW(1:1)),3)

Hope that makes more sense...Thanks!

"CJLuke" wrote:

Not quite...it looks like this:
A B C
1:
AccountSummary.................................... .................................................. ....
2: Portfolio : 00000119 - 613887041
on12/29/2006..............................................
3: Manager : RICHARD
UNDERWOOD......................................... ..........................
4:................................................ .................................................. ...................
5:..........................................Holdin gs..Coupon........................................ ...........
6: USTN.................................. 41750 .. 4.88
.................................................. .
7: AGCY.................................. 14000 .. 5.366
.................................................
8: CORP.................................. 27500 .. 5.337
.................................................. .

The formula I listed in the post will search for the defined string in the
IF function and return cosecutive values of that defined string. For
instance, if I wanted the USTN Holdings value, the formula would look like
this:

Index(A1:C8,SMALL(IF(A1:C8="USTN ",ROW(A1:C8)),ROW(1:1)),3)
<ctrl<shift<enter (to activate array)

Instead of simply searching for the consecutive values, I need it to search
by the account number at the top of the spreadsheet. Remember there are about
85 different entries like the one I listed in this reply. Some of them will
have the USTN field and others will not which presents a problem with the
above formula because it goes through the spreadsheet and returns consecutive
values. I need a way to search by account number so I can avoid this error.
Thanks a lot for replying because I know I am not the greatest explainer, and
this is a difficult question.





"T. Valko" wrote:

Is this what you have:

........Acct # .........Info
1.....1011A..........X
2..........................Y
3..........................Z
4.....3822A..........X
5..........................Y
6..........................Z

Biff

"CJLuke" wrote in message
...
I have a report produced by an analytic system that lists separate accounts
and their relevant information. The data is organized by account number so
the account number will appear at the top of the spreadsheet followed by
the
information for the account. The problem is that all of the output is
listed
in one spreadsheet. So, I have to find a function or create my own
function
that will look for the account number and return specific information that
is
listed for that specific account. The following formula gets me close, but
it
only returns the consecutive occurences of a certain parameter.

INDEX(A1:B7,SMALL(IF(A1:A7=A10,ROW(A1:A7)),ROW(1:1 )),2))

If you have a list like the following in A1:B7:

Ashish 234
Sanjay 334
Pongal 434
Ashish 534
Rajesh 634
Suresh 734
Ashish 834

Please email me if you have no idea what I am talking about and I will try
to explain it more fully. It is driving me crazy!!