Enter country code when country name is found

[sven_01]

Hi,
I want to have a formula that looks through a range of cells, and if a country is listed ( i.e United States) in a cell, then it will enter the 2 letter code in another cell (i.e US).
This would apply for approximately 70 countries.

 

[alansidman]

Build a table with the names of the seventy countries in one column and their 2 letter code in the next column.

Use the Vlookup function in your spreadsheet to find the 2 letter code. If you are unsure of the vlookup function, then go to https://www.techonthenet.com/excel/formulas/vlookup.php

 

[sven_01]

Thanks, but how do i deal with the instances where a country appears multiple times in the list. I did a vlook up formula but it did not work for multiple entries .Can anyone provide me the actual formula if the full list of countries runs from A1 through to A90 ( multiple occurrence of same country), where the table listing all countries is in E1 through to E70, and the matching country codes are in F1 through to F70 (country in E1 has matching 2 letter code in F1 and so on.)
thanks in advance.

 

[alansidman]

Look at this link and the UDF for multiple results on a lookup

http://www.excelfox.com/forum/showthread.php/4-Vlookup-to-Return-Multiple-Values?highlight=mlookup

 

[sven_01]

Thanks again Alansidman.
Sorry, but my excel experience is limited, and looking at this link didn't help me at all. (probably says a lot for my excel ability) Was hoping for the actual formula I could insert given the parameters in my 2nd post and the cells I provided.... can you / anyone else assist?

 

[SHIVANISHARMA1711]

Complaint ID	Company	Product	Issue	STATE_NAME	State	Submitted via	Date received	Date resolved	Timely response?	Consumer disputed?
1615767​	PHH Mortgage	Mortgage	Loan servicing, payments, escrow account		FL	Web	10/20/2015	10/20/2015	Yes	No
654223​	Ocwen	Mortgage	Loan servicing, payments, escrow account		NC	Web	2014-03-01​	2014-03-01​	Yes	No

Src					STATE	CODE
{ "STATE": "Alabama", "CODE": "AL"}			Alabama	AL
{ "STATE": "Alaska", "CODE": "AK"}			Alaska	AK
{ "STATE": "Arizona", "CODE": "AZ"}			Arizona	AZ
{ "STATE": "Arkansas", "CODE": "AR"}			Arkansas	AR
{ "STATE": "California", "CODE": "CA"}			California	CA
{ "STATE": "Colorado", "CODE": "CO"}			Colorado	CO
{ "STATE": "Connecticut", "CODE": "CT"}			Connecticut	CT
{ "STATE": "Delaware", "CODE": "DE"}			Delaware	DE
{ "STATE": "District of Columbia", "CODE": "DC"}		District of Columbia	DC
{ "STATE": "Florida", "CODE": "FL"}			Florida	FL
{ "STATE": "Georgia", "CODE": "GA"}			Georgia	GA
{ "STATE": "Hawaii", "CODE": "HI"}			Hawaii	HI
{ "STATE": "Idaho", "CODE": "ID"}			Idaho	ID
{ "STATE": "Illinois", "CODE": "IL"}			Illinois	IL
{ "STATE": "Indiana", "CODE": "IN"}			Indiana	IN
{ "STATE": "Iowa", "CODE": "IA"}			Iowa	IA
{ "STATE": "Kansas", "CODE": "KS"}			Kansas	KS
{ "STATE": "Kentucky", "CODE": "KY"}			Kentucky	KY
{ "STATE": "Louisiana", "CODE": "LA"}			Louisiana	LA
{ "STATE": "Maine", "CODE": "ME"}			Maine	ME
{ "STATE": "Montana", "CODE": "MT"}			Montana	MT
{ "STATE": "Nebraska", "CODE": "NE"}			Nebraska	NE
{ "STATE": "Nevada", "CODE": "NV"}			Nevada	NV
{ "STATE": "New Hampshire", "CODE": "NH"}		New Hampshire	NH
{ "STATE": "New Jersey", "CODE": "NJ"}			New Jersey	NJ
{ "STATE": "New Mexico", "CODE": "NM"}			New Mexico	NM
{ "STATE": "New York", "CODE": "NY"}			New York	NY
{ "STATE": "North Carolina", "CODE": "NC"}		North Carolina	NC
{ "STATE": "North Dakota", "CODE": "ND"}		North Dakota	ND
{ "STATE": "Ohio", "CODE": "OH"}			Ohio	OH
{ "STATE": "Oklahoma", "CODE": "OK"}			Oklahoma	OK
{ "STATE": "Oregon", "CODE": "OR"}			Oregon	OR
{ "STATE": "Maryland", "CODE": "MD"}			Maryland	MD
{ "STATE": "Massachusetts", "CODE": "MA"}		Massachusetts	MA
{ "STATE": "Michigan", "CODE": "MI"}			Michigan	MI
{ "STATE": "Minnesota", "CODE": "MN"}			Minnesota	MN
{ "STATE": "Mississippi", "CODE": "MS"}			Mississippi	MS
{ "STATE": "Missouri", "CODE": "MO"}			Missouri	MO
{ "STATE": "Pennsylvania", "CODE": "PA"}			Pennsylvania	PA
{ "STATE": "Rhode Island", "CODE": "RI"}			Rhode Island	RI
{ "STATE": "South Carolina", "CODE": "SC"}		South Carolina	SC
{ "STATE": "South Dakota", "CODE": "SD"}			South Dakota	SD
{ "STATE": "Tennessee", "CODE": "TN"}			Tennessee	TN
{ "STATE": "Texas", "CODE": "TX"}			Texas	TX
{ "STATE": "Utah", "CODE": "UT"}			Utah	UT
{ "STATE": "Vermont", "CODE": "VT"}			Vermont	VT
{ "STATE": "Virginia", "CODE": "VA"}			Virginia	VA
{ "STATE": "Washington", "CODE": "WA"}			Washington	WA
{ "STATE": "West Virginia", "CODE": "WV"}		West Virginia	WV
{ "STATE": "Wisconsin", "CODE": "WI"}			Wisconsin	WI
{ "STATE": "Wyoming", "CODE": "WY"}			Wyoming	WY