IF THEN

PAL · External Since: Oct 26, 2005 Posts: 20

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 · External Since: Jul 08, 2005 Posts: 16049

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

Niek Otten · External Since: Feb 23, 2004 Posts: 1957

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" <PAL RemoveThis @discussions.microsoft.com> wrote in message
news:217739B9-547B-42B0-A3E5-7BADCFE488B1@microsoft.com...
>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.

PAL · External Since: Oct 26, 2005 Posts: 20

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" <PAL.DeleteThis@discussions.microsoft.com> wrote in message
> news:217739B9-547B-42B0-A3E5-7BADCFE488B1@microsoft.com...
> >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.
>

Niek Otten · External Since: Feb 23, 2004 Posts: 1957

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" <PAL.DeleteThis@discussions.microsoft.com> wrote in message
news:82295782-6058-46B2-9328-8494FBF8D6BA@microsoft.com...
> 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" <PAL.DeleteThis@discussions.microsoft.com> wrote in message
>> news:217739B9-547B-42B0-A3E5-7BADCFE488B1@microsoft.com...
>> >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.
>>