Look up Function

[Guest]

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.

 

[Guest]

No you don't need code, look into Vlookup and Index, Match, hard to give you
an example without the actual data, just remember that with a Vlookup the
data you are looking up has to be the first column of the selection. Any
questions on them just ask.

 

[Guest]

My master table look like this

Japan 12 51
Japan 18 42
US 20 30

Where Japan is Col A, 12 is Col B, and 51 is Col C.
When I use vlookup as "=VLOOKUP(Japan,Sheet1!A:F,2,0)", the first row
returns 12, and the second row returns 12 as well.

Please kindly advise.

 

[Guest]

Kaci

Vlookup will only return 1 row. For the second entry you need to modify the
vlookup to find the next item.

The real question is there a maximun of 2 lines in the master table or more
and is the master table sorted so that the first find matched country
gurantees that the next line will show the same country if it is the same.
(ie ordered or not ordered).

 

[Bob Phillips]

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:$A20),""),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)