I have column A (first name) and column B (last name). Both are filled up with names (around fifty names). I have to look for a first name (Marry) in column A and wherever I find it, I have to modify the existing (ex: Stone) corresponding last name from column B with a different one (Kay) so in the end, instead of Marry Stone (like in the beginning) it will show Marry Kay. Help me please with a formula!

# Formula needed

### Discussion

## Answers

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".

### Discussion

Now I will go for lunch. When I come back I shall look for your reply. If you want to use the time to think ahead, look at the VLOOKUP function because that is what you will need. It will have to go into another column, however, because in column B you already have "Stone" and if you replace "Stone" with a formula then "Stone" will be gone. Which gives me an idea for another possible solution: just replace all the names with formulas, but then you would have to have two ranges, one with correct names and another with wrong ones. Do you have that? Or is that the required solution, to create a second corrected range? It would indeed help if you would post your workbook. You can attach it to the original question, if you like.

I am a rookie regarding Excel, but I want to learn so I'm solving tests...or at least I'm trying. This isn't homework, it's just something that bothers me because I can't find a way to solve it. Here is how I tried: I selected all the names from both columns, than I pressed the function key. I was thinking to use LOOKUP for Marry in column A, after that to use IF ...like if Marry in column A than use SUBSTITUTE with Kay in column B. But I don't know how to connect all this functions, or if this is the correct way to do it.