Replace abbreviation with full file name

[griswold]

I am looking for a way to scan a column in a sheet and if the code matches with a longer version of the code in a separate column, replace the short code with the longer code. For example in column 1 rows 1-10 contain code "ABCD". Search column 2 and find "Code_Type_ABCD" and replace "ABCD" in column 1 with the value "Code_Type_ABCD" from column 2. There are 50 different codes on sheet 1 that will need to be searched for and replaced with values from sheet 2. Or even set a formula in column 3 that would list the correct code from column 2 on each row.

[JohnTopley]

Can you post a sample sheet with all codes?

[griswold]

test.xlsx
The first column contains the codes with the second containing the full code name. The abbreviations in the first column are the final portion of the full name.

[JohnTopley]

I am confused! Column A in sheet1 contains many repeated codes e.g ADJB and Column B in sheet1 seems to contain unrelated codes.

example from 2 returns code in red

ADJB== SEDC_GA_RPT_HDLA== SEDC_CA_RPT_ADJB

The formula I used was:

=INDEX(Sheet2!$A:$A,MATCH("*"&A1&"*",Sheet2!$A:$A,0))

[JohnTopley]

Test file attached - full names in Column C as you cannot replace column A with a formula.

Hope this what you want.

[griswold]

Column B contains the full length names of the codes in column A. Not in order however. Each code in Column A represents an individual report so there will be duplicates. I need something that will look at the value in column A, search column B for a value that has the same ending and then populate that value in Column C.
It looks like the formula you posted does just that.

[griswold]

Thank you very much!