Automating conform of cell contents so they MATCH

[Guest]

I have a list of countries in column 1 with corresponding values in column
2. I have a second spreadsheet with the same countries (usually spelled the
same way) in column 1 and a new set of values in column 2.

For example:

SS1 cols 1,2:

Afghanistan $50,000
Bangladesh $25,000
Bangladesh-Dhaka $10,000

SS2 cols 1,2:

Afghanistan $100,000
Bangladesh $35,000
Bangladesh-Dhaka $20,000

I would like to automatically have SS1 result in the following in cols. 1,2,3:

Afghanistan $50,000 $100,000
Bangladesh $25,000 $35,000
Bangladesh-Dhaka $10,000 $20,000

I use the functions to automate this as follows:

=IF(ISNA(MATCH(A1,Sheet2!$A$1:$A$100,0)),"NotFound",VLOOKUP(A1,Sheet2!$A$1:$B$100,2,0))

And is works ok.

My question:

any countries where the spelling is slightly different on each spreadsheet,
is there a way to automate the correction process to create an exact match.
For example, to auto correct Bangladesh - Dhaka to Bangladesh-Dhaka (without
the spaces on either side of the hyphen which prevents a match). Or
elimination of leading or trailing spaces which also prevents a match, or
correction of Bangladesh (Dhaka) to Bangladesh-Dhaka to enable a match.

I'm trying to avoid human intervention. There are probably only 5-6
variations (extra spaces; parenthesis to hyphens, etc.) which would have to
be checked and corrected. Is there a macro or something which could
accomplish this?

Or can MATCH be changed so it finds the closest match even if not exact?

Etc.?

hopefully, some or all of the above is possible and can be automated.
appreciate
your help. thanks

 

[R.VENKATARAMAN]

normally this will work
in sheet1 C1 type
=VLOOKUP(A1,Sheet2!A1:B3,2,FALSE)
and copy the formula down.

see wheteher this works
if it does not work
the error may be in cells having the country names
you must have taken these from some web database so the cells where the
names of the countries are, some non printable characters are added. this
can be seen by pearson's add in view cell contents (the addin is called
hexchars)
these have to be removed by using the macro <trimall> by Mcritchie in the
following url
http://www.mvps.org/dmcritchie/excel/join.htm.
and then use the formula otherwise you may get #N/A

if you are only having few country names, in one sheet retype the names and
copy them in the other sheet.and then use the romula

 

[Claud Balls]

In your VLOOKUP formula, leave the zero out. For a description why
search Excel help for VLOOKUP and check out the range_lookup value.