Extract data from one worksheet

[Guest]

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?

 

[Ardus Petus]

Use Data>Filter>Advanced filter

As Criteria range enter Country as column header in A1, enter USA in A2
Criteria range: $A$1:$A$2

HTH

 

[Bob Phillips]

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)

 

[Guest]

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]

Same formula, just copy it across as well as down.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Guest]

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