Pick and Choose

sj1601

Board Regular
Joined
Aug 12, 2006
Messages
96
I have a list of all the countrys' codes (column). I have a list of 17 countries that I am interested in which has to show up on my report.

How can I create a dynamic formula to pick up these 17 names from this list?

Thanks.

sj1601
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

lenze

Legend
Joined
Feb 18, 2002
Messages
13,690
Do you mean you need to pick up adjacent data from these names in your column? What is the structure of your data/report? Will these names change? How about some sample data?

lenze
 

sj1601

Board Regular
Joined
Aug 12, 2006
Messages
96
More details:

I have the foll data:

Country Code Country Name
FR
GB
IN
and so on

I want to pick up the country code and call it by its name and place it under the country name column,example FR is France, GB is UK etc.

The country name col is not necessarily adjacent to the Country code col.

Please lemme know if you have more questions.

Thanks for your help.
 

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,721

ADVERTISEMENT

Assuming that A2:A18 contains the country code, and B2:B18 contains the corresponding country name, try...

=INDEX(B2:B18,MATCH(D2,A2:A18,0))

...where D2 contains the country code of interest.

Hope this helps!
 

lenze

Legend
Joined
Feb 18, 2002
Messages
13,690
So, do you want to enter a country code and have it display the country name ala VLOOKUP?

lenze
 

sj1601

Board Regular
Joined
Aug 12, 2006
Messages
96

ADVERTISEMENT

The country code is already available in a column.

I wanna create the country name col with my choice of countries.
 

sj1601

Board Regular
Joined
Aug 12, 2006
Messages
96
Domenic,

It didn't work! :(

One way that I am thinking of is having numerous IF statements for each country. But that would be too many IF formulas in one cell. I am sure there is an easier and cleaner way. I just have tofigure that out.

Help.

Thanks.
 

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,721
What do you mean by "it didn't work"? Did it return an error value or did it return an incorrect result? Did you set up a source table? Which cells contain the country code, and which cells contain the corresponding country name? Also, can you post the exact formula you tried?
 

sj1601

Board Regular
Joined
Aug 12, 2006
Messages
96
I got the #N/A error.

Like I mentioned before the country code is given to us in one column (say D1:D10000). We do not have a list for the Country Names we want in the given data. (Should we create one?)
The formula we set up should return the country name of that country code. For instance, the formula should say, if country code is FR then country name is France, if not then if country code is GB then country name is UK and if it is none of the 17 countries we are interested in then blank for all the 10000 country codes. (Note: there can be multiple data points for the same country code, that's ok.)

I hope this clears things a little more.

Please lemme know if you have questions.

sj1601
 

Forum statistics

Threads
1,141,222
Messages
5,705,109
Members
421,378
Latest member
CarlosDuran

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top