Pick and Choose

[sj1601]

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

 

[sj1601]

Anyone?

 

[lenze]

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]

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]

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]

So, do you want to enter a country code and have it display the country name ala VLOOKUP?

lenze

 

[sj1601]

The country code is already available in a column.

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

 

[sj1601]

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]

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]

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