Hi, Bob,

Thanks for your help, I copy the formula and it is working. Please excuse me
as a beginner but yet trying to get the job done.

Can you show me the next formula to copy the corresponding cell to the next
column, that means, have both country and city as a result,

USA SEA
USA SFO

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?