Hi, Bob;

SKY again. After reading your formula in detail, I simply copy your formula
to the next cell, and be able to get CITY now.

Really appreciate your help, you are genius!

Yours,
SKY

"Bob Phillips" wrote:

In another column add

=IF(NOT(ISERROR(SMALL(IF($A$1:$A$40="USA",ROW($1:$ 40)),ROW(1:1))-MIN(ROW($A$
1:$A$40)))),
INDEX(A$1:A$40,SMALL(IF($A$1:$A$40="USA",
ROW($1:$40)),ROW(1:1))-MIN(ROW($A$1:$A$40))+1,1),"")

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

Copy this formula acroos to the next column, and down as far as you need.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"SKY" wrote in message
...
I would like to extract matched data from one worksheet and put into
another.
For example, a table contains two columns, country and city,
Country City
CN SZ
CN BJ
USA SEA
USA SFO

On a blank worksheet, enter "USA" in A1, how can I copy only the last two
rows?
I try INDEX & MATCH functions, it always finds the first row. How can I
tell
the Excel to make a on-going MATCH until it finish an array?