As I am looking at your second function closer now, I am appreciating it much
more. Maybe one of the conditions is not necessary. I know you are telling
me to concatenate the "A" and the k-th smallest value in the data set. This
is pretty much what I am after. I think after I get down 30 rows, which is
the number returned by your first function, the Count-Search function, the
second part of the Small-IsNumber-Search function is being evaluated, and
this is basically:
If k ‰¤ 0 or if k exceeds the number of data points, SMALL returns the #NUM!
error value (from the MS Help tool). After I go down 30 rows, I get all
#NUM! Alternatively, according to the Help tool, the array could be empty,
and thus SMALL is returning the #NUM! error value. Do you know of any way
around this? The array of the first value sought has ended after 30 rows,
but that I want to find the array of the second, value, the third, etc.

Regards,
Ryan---


--
RyGuy


"T. Valko" wrote:

I'm having a hard time trying to "visualize" where you're inserting rows.

A couple of observations:

Some of the references in the formula need to be absolute:

=IF(ROWS(C2:C$2)<=B2,"A"&SMALL(IF(ISNUMBER(SEARCH( A2,'Import
Sheet'!A$1:A$65000)),ROW('Import Sheet'!A$1:A$65000)),ROWS(C2:C$2)),"")

B2 should be B$2
A2 should be A$2

Also, depending on where you're inserting rows will change these
expressions:

=IF(ROWS(C2:C$2)
ROWS(C2:C$2)

If these expressions evaluate to be greater than the number returned by this
formula:

=COUNT(SEARCH($A7,'Import Sheet'!$A$1:$A$65000))

Then the formula returns a blank. Is that what you mean by stopped counting?

--
Biff
Microsoft Excel MVP


"ryguy7272" wrote in message
...
I have a follow-up question for T Valko.

Can you offer some kind of modification, or alternative, to the second
function (below) that you gave me yesterday?

In the form that you gave all functions to me, all functions worked great!
However, I made some modifications, and now I have a problem here.

I used your first function, and ended up counting the number of matches,
between items in my Column A on the active sheet, and items in Column A on
the Import Sheet:
=COUNT(SEARCH($A7,'Import Sheet'!$A$1:$A$65000))
CSE, of course. This function is in my Column B of the active sheet, and
this works fine.

Then, I used your second function, and found all cell addresses on the
Import Sheet, which is what I wanted:
=IF(ROWS(C2:C$2)<=B2,"A"&SMALL(IF(ISNUMBER(SEARCH( A2,'Import
Sheet'!A$1:A$65000)),ROW('Import Sheet'!A$1:A$65000)),ROWS(C2:C$2)),"")
This function is in my Column C.

Finally, I used your third function:
=INDIRECT("'Import Sheet'!"&E2)
This function is in my Column D.

I then inserted rows in Column A, sort of as place holders, for each of
the
numbers which are in Column B of the active sheet. This allowed me to
show
each of the cell addresses (Column C of my active sheet) and each bit of
text
from these cell addresses (Column D of my active sheet). So, I thought
everything was going to be roses, but then I realized the second function
stops counting when I inserted those rows in my Column A! Do you have any
idea what could be causing this? Does anyone have an alternative to look
up
cell addresses, such as Address & Vlookup? Tried a few combinations of
things and nothing seemed to work.....


Cordially,
Ryan---


--
RyGuy