Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Look up Function
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Look up Function
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. -- -John Northwest11 Please rate when your question is answered to help us and others know what is helpful. "kaci" wrote: 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Look up Function
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. "kaci" wrote: 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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Look up Function
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). -- Hope this helps Martin Fishlock, Bangkok, Thailand Please do not forget to rate this reply. "kaci" wrote: 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. "kaci" wrote: 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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Look up Function
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel Data Validation/Lookup function does function correcty | Excel Worksheet Functions | |||
copy of excel file not showing formulal/function in the function b | Excel Discussion (Misc queries) | |||
LINKEDRANGE function - a complement to the PULL function (for getting values from a closed workbook) | Excel Worksheet Functions | |||
Excel - User Defined Function Error: This function takes no argume | Excel Programming | |||
User-Defined Function pre-empting Built-in Function? How to undo???? | Excel Programming |