Select a block of cells in a column that can contain all possible values (10
if you think there can be upto 10 items of one country, or whatever).

Then, in the formula bar (not in the cell) enter

=IF(ISERROR(SMALL(IF($A$1:$A$20="Japan",ROW($A1:$A 20),""),ROW($A1:$A20))),"",
INDEX(A$1:A$20,SMALL(IF($A$1:$A$20="Japan",ROW($A1 :$A20),""),ROW($A1:$A20))))

as an array formula, so Ctrl-Shift-Enter to commit it, not just Enter.

Then copy that block of cells across two columns to get the other data.

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"kaci" wrote in message
...
Hello,

I have created a Master data worksheets, and one of the column in the
master
worksheets indicates countries, for example Col A.
Then I have created a blank table in Worksheet 1, and in worksheet 1,
column
B3 indicates the country code.
How can I look up the country code in Worksheet 1, then when the country
in
worksheet 1 matches the Col A in the master worksheet, worksheet 1 will
display the data value of Col B, C, D, E in the maste worksheet. Same
countries appear different times in Col A of the master worksheet.

Do i need to write visual basic coding?

Million thanks.