Selected Answer
By your description this is a task for VBA because a worksheet function can't change any cell's value. However, the nearest to a solution would be the use of the VLOOKUP function. VLOOKUP has the following instruction.
"Find the LookUp-Value in the first column of the LookUp-Range using the LookUp-Match rule and return the value in the Return-Column of the row where you found it." For example, =VLOOKUP([LookUp-Value], [LookUp-Range], [Return-Column], [LookUp-Match]) which might look like this,
=VLOOKUP(A2, A3:A50, 2, FALSE)
Each of the 4 arguments has its own rules which you might know or read up in Excel Help or online at any time. When you type "=VOOLKUP(" Excel will give you hints, too.
- LookUp-Value
In the above example it is A2, but in your task it is "Marry", except that it isn't "Marry". It's "any name containing Marry". That you can express as "*" & "Marry" & "*" where the asteric is a placeholder for any number of characters from 0 to 255 (not sure about the maximum). Therefore =VLOOKUP( "*" & "Marry" & "*" , or simply =VLOOKUP( "*Marry*" ,will consider anything a match that contains "Marry". Actually, it will also match "marry" or "MARRY" ETC.
- LookUp-Range
In your example that would be A3:B50 or, better $A$3:$B$50 if you intend to copy the formula to other cells (which you don't in this case). Observe that the range excludes A2 because that is the row where you have the wrong name. If you define the range as A2:B50 the match will be found in A2. Observe that the LookUp-Range has 2 columns. VLOOKUP will only look in the first. The otehrs are for the return value. You could have many more to the right of the first. In this case you want to return a value from column B. So, you only need 2 columns.
- Return-Column
In your example, you want to return the name from column B which is the second column in A3:B50. If the LookUp-Range was X3:Y50 then column 2 would define column Y. If you define a column which doesn't exist in the LookUp-Range and error will result.
- LookUp-Match
You can choose between "Approximate" (TRUE) and "Exact" (FALSE). True is useful when looking for numbers, including dates. In your example the approximation is included in the LookUp-Value, and now you want an exact result.
If your formula can't find a match it will return an error. Therefore you might embed it in an IFERROR function. The IFERROR function has this description. "If my Expression returns an error return Something_Useful instead", for example, =IFERROR[Expression], [Something_Useful]). In this example, the above formula is the Expression and you can decide what is Something_Useful. Actually anything would be better than "#N/A". So, why not "not found".
So, your formula might look like this.
=IFERROR(VLOOKUP("*Marry*",A3:B50,2,FALSE), "not found")
I realise that this solves only a part of your problem. Here is a variation of the above which you should now be able to read on your own.
=IFERROR(VLOOKUP("*A2*",A3:B50,2,FALSE), B2)
As a hint, I placed the word "Marry" in A2 and "Anne-Mary" somewhere further down in column A. So, you can place the above formula in C2 and get a different result by adding an r in "Anne-Mary".