Am I on the right path
 

Hi there,
I have a list of phone numbers by country including number, name and
department and saved them in ranges i.e. a1:b20 = Germany range
a30:b55=France etc

In the second sheet I need to pull in the name and department. I have used
Vlookup which works a treat for one country
=VLOOKUP(P1,'Numbers'!Germany,2,FALSE)

However I wonder how to add if country = france etc then lookup that range
etc for all my countries. Should I use a macro? or IF? or place the
different version of the lookup into each country and copy down?

Thanks, Teddy

Am I on the right path
 

Hi Teddy ! :) It would be better (easier) to write a macro with .Find instead
of Worksheerfunction.VLOOKUP to do the job.



"Teddy" wrote:

Hi there,
I have a list of phone numbers by country including number, name and
department and saved them in ranges i.e. a1:b20 = Germany range
a30:b55=France etc

In the second sheet I need to pull in the name and department. I have used
Vlookup which works a treat for one country
=VLOOKUP(P1,'Numbers'!Germany,2,FALSE)

However I wonder how to add if country = france etc then lookup that range
etc for all my countries. Should I use a macro? or IF? or place the
different version of the lookup into each country and copy down?

Thanks, Teddy

Am I on the right path
 

Many thanks for your quick answer. Do you have any suggestions how to do
this or could point me to some sample code? Thanks again.T.

"Vijay Chary" wrote:

Hi Teddy ! :) It would be better (easier) to write a macro with .Find instead
of Worksheerfunction.VLOOKUP to do the job.



"Teddy" wrote:

Hi there,
I have a list of phone numbers by country including number, name and
department and saved them in ranges i.e. a1:b20 = Germany range
a30:b55=France etc

In the second sheet I need to pull in the name and department. I have used
Vlookup which works a treat for one country
=VLOOKUP(P1,'Numbers'!Germany,2,FALSE)

However I wonder how to add if country = france etc then lookup that range
etc for all my countries. Should I use a macro? or IF? or place the
different version of the lookup into each country and copy down?

Thanks, Teddy

Am I on the right path
 

If the countries are in a column of their own then select the top row of
your data and use

datafilterautofilterfilter on the column

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Teddy" wrote in message
...
Hi there,
I have a list of phone numbers by country including number, name and
department and saved them in ranges i.e. a1:b20 = Germany range
a30:b55=France etc

In the second sheet I need to pull in the name and department. I have
used
Vlookup which works a treat for one country
=VLOOKUP(P1,'Numbers'!Germany,2,FALSE)

However I wonder how to add if country = france etc then lookup that range
etc for all my countries. Should I use a macro? or IF? or place the
different version of the lookup into each country and copy down?

Thanks, Teddy

Am I on the right path
 

Don,
Many thanks for your help. Do you mean to first filter by country then
paste in each vlookup changing the named range then copy for that country and
repeat for each country?
Teddy

"Don Guillett" wrote:

If the countries are in a column of their own then select the top row of
your data and use

datafilterautofilterfilter on the column

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Teddy" wrote in message
...
Hi there,
I have a list of phone numbers by country including number, name and
department and saved them in ranges i.e. a1:b20 = Germany range
a30:b55=France etc

In the second sheet I need to pull in the name and department. I have
used
Vlookup which works a treat for one country
=VLOOKUP(P1,'Numbers'!Germany,2,FALSE)

However I wonder how to add if country = france etc then lookup that range
etc for all my countries. Should I use a macro? or IF? or place the
different version of the lookup into each country and copy down?

Thanks, Teddy

Am I on the right path
 

Since I can't see your data, I don't know. Send to my address below if
desired along with full explanation and before/after examples.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Teddy" wrote in message
...
Don,
Many thanks for your help. Do you mean to first filter by country then
paste in each vlookup changing the named range then copy for that country
and
repeat for each country?
Teddy

"Don Guillett" wrote:

If the countries are in a column of their own then select the top row of
your data and use

datafilterautofilterfilter on the column

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Teddy" wrote in message
...
Hi there,
I have a list of phone numbers by country including number, name and
department and saved them in ranges i.e. a1:b20 = Germany range
a30:b55=France etc

In the second sheet I need to pull in the name and department. I have
used
Vlookup which works a treat for one country
=VLOOKUP(P1,'Numbers'!Germany,2,FALSE)

However I wonder how to add if country = france etc then lookup that
range
etc for all my countries. Should I use a macro? or IF? or place the
different version of the lookup into each country and copy down?

Thanks, Teddy

Am I on the right path
 

Try it this way:

=VLOOKUP(P1,'Numbers'!A:B,2,FALSE)

That should look up all the numbers in column A, not just those
related to Germany.

Hope this helps.

Pete

On Dec 21, 10:52*am, Teddy wrote:
Hi there,
I have a list of phone numbers by country including number, name and
department and saved them in ranges i.e. a1:b20 = Germany range
a30:b55=France etc *

In the second sheet I need to pull in the name and department. *I have used
Vlookup which works a treat for one country
=VLOOKUP(P1,'Numbers'!Germany,2,FALSE)

However I wonder how to add if country = france etc then lookup that range
etc for all my countries. *Should I use a macro? or IF? or place the
different version of the lookup into each country and copy down?

Thanks, Teddy *

Am I on the right path
 

This is great - much more simplified. Didn't realise you could just
reference the columns A:B .
Many thanks,
T

"Pete_UK" wrote:

Try it this way:

=VLOOKUP(P1,'Numbers'!A:B,2,FALSE)

That should look up all the numbers in column A, not just those
related to Germany.

Hope this helps.

Pete

On Dec 21, 10:52 am, Teddy wrote:
Hi there,
I have a list of phone numbers by country including number, name and
department and saved them in ranges i.e. a1:b20 = Germany range
a30:b55=France etc

In the second sheet I need to pull in the name and department. I have used
Vlookup which works a treat for one country
=VLOOKUP(P1,'Numbers'!Germany,2,FALSE)

However I wonder how to add if country = france etc then lookup that range
etc for all my countries. Should I use a macro? or IF? or place the
different version of the lookup into each country and copy down?

Thanks, Teddy

Am I on the right path
 

You're welcome - glad it worked for you.

Pete

On Dec 21, 10:40*pm, Teddy wrote:
This is great - much more simplified. *Didn't realise you could just
reference the columns A:B .
Many thanks,
T



"Pete_UK" wrote:
Try it this way:

=VLOOKUP(P1,'Numbers'!A:B,2,FALSE)

That should look up all the numbers in column A, not just those
related to Germany.

Hope this helps.

Pete

On Dec 21, 10:52 am, Teddy wrote:
Hi there,
I have a list of phone numbers by country including number, name and
department and saved them in ranges i.e. a1:b20 = Germany range
a30:b55=France etc *

In the second sheet I need to pull in the name and department. *I have used
Vlookup which works a treat for one country
=VLOOKUP(P1,'Numbers'!Germany,2,FALSE)

However I wonder how to add if country = france etc then lookup that range
etc for all my countries. *Should I use a macro? or IF? or place the
different version of the lookup into each country and copy down?

Thanks, Teddy *- Hide quoted text -

- Show quoted text -