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?

Use DataFilterAdvanced filter

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

HTH
--
AP

"SKY" a écrit dans le message de
...
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?

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?

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?

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

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"SKY" wrote in message
...
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?

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?