Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF THEN
I have two worksheets. The first one is a list of countries (~25 countries;
LOV!A1:A25) down one column. The second one is a a multi-column, multi-row list, with one of the columns being country. I have inserted a another column in order to create regions for each row. Example: If B5=USA, I want it to return, "North America"... If B5=Canada, I want it to return, "North America" If B5=Spain, I want it to return, "Europe" IF B6=Japa, I want it to return, "Asia" Seems like this is a complex if, but not able figure it out. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF THEN
Look in HELP for the VLOOKUP() function
Here is a tutorial: http://www.contextures.com/xlFunctions02.html -- Kind regards, Niek Otten Microsoft MVP - Excel "PAL" wrote in message ... I have two worksheets. The first one is a list of countries (~25 countries; LOV!A1:A25) down one column. The second one is a a multi-column, multi-row list, with one of the columns being country. I have inserted a another column in order to create regions for each row. Example: If B5=USA, I want it to return, "North America"... If B5=Canada, I want it to return, "North America" If B5=Spain, I want it to return, "Europe" IF B6=Japa, I want it to return, "Asia" Seems like this is a complex if, but not able figure it out. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF THEN
To see if I was on the right track. So I grouped USA and Canada calling them
North America by highlighting them and giving them a name. Then I created this formula =(IF(VLOOKUP(A3,NorthAmerica,1,FALSE),"North America","")) and it gave me the #!Value error. I don't think I fully understand how to use this. "Niek Otten" wrote: Look in HELP for the VLOOKUP() function Here is a tutorial: http://www.contextures.com/xlFunctions02.html -- Kind regards, Niek Otten Microsoft MVP - Excel "PAL" wrote in message ... I have two worksheets. The first one is a list of countries (~25 countries; LOV!A1:A25) down one column. The second one is a a multi-column, multi-row list, with one of the columns being country. I have inserted a another column in order to create regions for each row. Example: If B5=USA, I want it to return, "North America"... If B5=Canada, I want it to return, "North America" If B5=Spain, I want it to return, "Europe" IF B6=Japa, I want it to return, "Asia" Seems like this is a complex if, but not able figure it out. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF THEN
On the Lov worksheet, add another column (a new column B???).
Then would look like: --A---- ----B-------- USA North America Canada North America Spain Europe Japan Asia Then you could use this kind of formula: =vlookup(b5,lov!a:b,2,false) to return the continent for that country. PAL wrote: I have two worksheets. The first one is a list of countries (~25 countries; LOV!A1:A25) down one column. The second one is a a multi-column, multi-row list, with one of the columns being country. I have inserted a another column in order to create regions for each row. Example: If B5=USA, I want it to return, "North America"... If B5=Canada, I want it to return, "North America" If B5=Spain, I want it to return, "Europe" IF B6=Japa, I want it to return, "Asia" Seems like this is a complex if, but not able figure it out. -- Dave Peterson |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF THEN
Make 2-column table in which you connect countries to a regio, like this:
United States | North America Canada | North America Spain | Europe etc. In this example the table is in A1:C3 With the country in D1, use this formula: =VLOOKUP(D1,$A$1:$B$3,2,FALSE) -- Kind regards, Niek Otten Microsoft MVP - Excel "PAL" wrote in message ... To see if I was on the right track. So I grouped USA and Canada calling them North America by highlighting them and giving them a name. Then I created this formula =(IF(VLOOKUP(A3,NorthAmerica,1,FALSE),"North America","")) and it gave me the #!Value error. I don't think I fully understand how to use this. "Niek Otten" wrote: Look in HELP for the VLOOKUP() function Here is a tutorial: http://www.contextures.com/xlFunctions02.html -- Kind regards, Niek Otten Microsoft MVP - Excel "PAL" wrote in message ... I have two worksheets. The first one is a list of countries (~25 countries; LOV!A1:A25) down one column. The second one is a a multi-column, multi-row list, with one of the columns being country. I have inserted a another column in order to create regions for each row. Example: If B5=USA, I want it to return, "North America"... If B5=Canada, I want it to return, "North America" If B5=Spain, I want it to return, "Europe" IF B6=Japa, I want it to return, "Asia" Seems like this is a complex if, but not able figure it out. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|