Hi!

1) How is Rows($1:1) evaluated?

ROWS() returns the number of rows in the referenced range or array. The
range arguments can be entire rows like 1:10 or cell references like A1:A10
or array constants like {1,2,3,4,5,6,7,8,9,10}. All of those examples
evaluate exactly the same. The result would be 10. When you use cell
references like A1, The column reference A is ignored.

2) What is the significance of rows<=count?

That is being used as a pseudo error trap.

A "typical" error trap might look like this: (some might even include the
INDEX in ISERROR which is not necessary since that is not where an error
will be generated)

=IF(ISERROR(SMALL(P:P,ROWS($1:1))),"",INDEX(Q:Q,SM ALL(P:P,ROWS($1:1))))

This error trap:

=IF(ROWS($1:1)<=COUNT(P:P)

Is slightly shorter and is overall, more efficient. There will only be a
certain number of matches to be returned so that means the formula will need
to be copied to a certain number of cells. That string of formula compares
the number of matches to the number of cells that the formula is copied to.
If the number of cells being copied to is less than or equal to the number
of matches, the formula returns the appropriate match, otherwise, returns a
formula blank: "".

When the error trap in the below formula evaluates to FALSE then the formula
has to process this expression: SMALL(P:P,ROWS($1:1)) twice:

=IF(ISERROR(SMALL(P:P,ROWS($1:1))),"",INDEX(Q:Q,SM ALL(P:P,ROWS($1:1))))

3) Index takes a range, row num, column num as parameters.
why is column num not specified?

Because we're only indexing a single column. If the column_num argument is
ommitted, it defaults to 1.

Biff

wrote in message
ups.com...
Could you please explain the second formula, that is,
=IF(ROWS($1:1)<=COUNT(P:P),INDEX(Q:Q,SMALL(P:P,ROW S($1:1))),"")

Specifically,
1) How is Rows($1:1) evaluated?
2) What is the significance of rows<=count?
3) Index takes a range, row num, column num as parameters. why is
column num not specified?

Biff wrote:
Hi!

If you want to use a helper column: (as I recall, you had about 7000
symbols)

Assume the helper column is column P.

Assume the symbols are in Q1:Rn.

Enter this formula in P1:

=IF(COUNTIF(R:R,Q1),ROW(),"")

Copy down to Qn.

Extract the matches:

Enter this formula in some cell, say, T1:

=IF(ROWS($1:1)<=COUNT(P:P),INDEX(Q:Q,SMALL(P:P,ROW S($1:1))),"")

Copy down until you get blanks meaning all the matches have been
extracted.

Biff

"Manfred" wrote in message
...
I have a list of stock symbols (filtered using a separate program) in
column Q, along with another list in column R that MAY OR MAY NOT have
some
of the same symbols. My objective is to place the symbols that match
(from
columns Q and R) in a separate column (column S). Is it possible for
Excel
to perform this function, and if so, can someone offer the formula for
doing so? Any help would be appreciated.