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
 

Some videos you may like

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

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,298

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,298
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
 

Watch MrExcel Video

Forum statistics

Threads
1,108,630
Messages
5,523,991
Members
409,554
Latest member
denistrevisan

This Week's Hot Topics

Top