In this lesson, I’ll demonstrate 7 effective approaches for finding and replacing multiple values in Excel. These techniques will enable you to easily find and replace multiple values over huge datasets in Excel. You will master several crucial Excel features and functions during this article, which will come in extremely handy for any assignment using Excel.

## 📁 Download Excel File

The sample worksheet that was used during the discussion is available to download for free right here.

## Learn to Find and Replace Multiple Values in Excel Using These 7 Methods

I have demonstrated 7 approaches to find and replace multiple values in Excel in this Article. Every Approach is detailed with accurate step-by-step pictures. Therefore Going through all the methods you will better understand which one to use in a specific situation for efficiency. The Excel file used for all the solutions down below is also attached to this article it will help you to understand better.

**Method 1**

### 1. Using Find and Replace Tool

This is one of the easiest approaches to find and replace a certain value in Excel. We will be using **Find and Select Tool** and complete our desired task of replacing data.

⬇️⬇️ **STEPS** ⬇️⬇️

- First, we will select the
**Find and Select Tool**from the ribbon, it’s found in the Home option and located at the rightmost side of the ribbon. We will select the Replace option from the Find and Select Tool like the picture.

- Now a new window will appear, type in what you want to find from the worksheet and insert the replacing value. Then simply click on
**Replace All.**The**Find All**button will show you details of every cell that has the value which you are searching for.

- For instance, you’ll see the following Scenario.

- We have successfully replaced multiple values in an Excel datasheet.

**📕 Read More: ****3 Quick Ways to Replace Text After Specific Character**

**Method 2**

### 2. Using SUBSTITUTE Function

The **Substitute Function** can be handy in solving this find and replacing multiple values Excel problem. We will be using a formula using multiple **Substitute Functions**. The process is given below with pictures.

⬇️⬇️** STEPS **⬇️⬇️

- First, you need to make a list of the values you are willing to replace as well as the values you are going to replace with. Then you need to select a cell where you want your answers to store. Then simply copy the following formula.

**=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($B$6:$B$12, $D$6, $E$6),$D$7, $E$7), $D$8, $E$8), $D$9, $E$9)**

- Now after that simply tap
**Enter**.

- Now
**Copy Formula Down**along the column.

Here we replaced 4 values which are short-term of the name of some states to their actual form.

**🔨 ****Formula Breakdown**

**👉 SUBSTITUTE($B$6:$B$12, $D$6, $E$6)**

The First part of the command **$B$6:$B$12** will result in selecting the text or reference to a cell that contains text for which we want to substitute characters and it’s column **B**, then the next term **$D$6 **the old text, and finally **$E$6 **is the text we want to replace the old text with if it’s found on the reference cell of the row.

**👉 SUBSTITUTE(SUBSTITUTE($B$6:$B$12, $D$6, $E$6),$D$7, $E$7)**

This will search for other options to match. Here we don’t get a match as **$D$7** doesn’t have any data that is in the reference cell **$B$6 ** for row **6**.

**👉 SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($B$6:$B$12, $D$6, $E$6),$D$7, $E$7), $D$8, $E$8)**

This command will also search for other options to match. Here we don’t get a match as **$D$8** doesn’t have any data that is in the reference cell **$B$6** for row **6**.

**👉 SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($B$6:$B$12, $D$6, $E$6),$D$7, $E$7), $D$8, $E$8), $D$9, $E$9)**

Lastly, this command will again search for other options to match. Here we don’t get a match as **$D$9** has no information that corresponds to the reference cell **$B$6** for row **6**.

**📕 Read More: ****13 Ways to Search for Text in Range in Excel**

**Method 3**

### 3. Applying XLOOKUP Function

The **XLOOKUP Function** is a good option if you use Excel 365. The **XLOOKUP Function** looks for matches between two ranges or arrays and returns the matching item from the first range or array. We will be using the previous dataset in this approach also.

⬇️⬇️ **STEPS **⬇️⬇️

- Start with selecting the cell you want the result from.
- Now carefully type or copy the following formula.

**=XLOOKUP($D6,$B$6:$B$10,$C$6:$C$10,$D6)**

- Hit
**Enter**for the result.

**📕 Read More: ****How to Find and Replace Using Wildcards in Excel**

**Method 4**

### 4. Utilizing LAMBDA Function

Excel offers a specific function that enables building customized functions using a conventional formula syntax **For Microsoft 365** subscribers. This function named **LAMBDA** being a special feature doesn’t work on every version of Excel. So use it if you have **Microsoft 365 **Subscription. In other words, a very handy feature for excel power users. We are going to demonstrate the function below. We are using this function here to find and replace multiple values.

⬇️⬇️ **STEPS **⬇️⬇️

- In the beginning, use the ribbon to go to the
**Name Manager**from the**Formulas**.

- After clicking on Name Manager, a new window will appear
**Select New**for defining a new function.

- Now another window will pop up. Give the custom function a
**Name**. I am using**MultiReplace**for instance. Next copy down the following formula and paste in the**Refers to:**option and press**OK**.

**=LAMBDA(text_value, old_value, new_value, IF(old_value<>””, MultiReplace(SUBSTITUTE(text_value, old_value, new_value), OFFSET(old_value, 1, 0), OFFSET(new_value, 1, 0)), text_value))**

- Now
**select**the cell you want to store the replaced data and copy down the following formula. Here we are using our freshly baked function.

**=MultiReplace(B6:B12,$D$6,$E$6)**

- Press
**Enter**for the result.

**Copy down the formula**for the total result.

**🔨 ****Formula Breakdown**

**👉 LAMBDA(text_value, old_value, new_value, IF(old_value<>””, MultiReplace(SUBSTITUTE(text_value, old_value, new_value), OFFSET(old_value, 1, 0), OFFSET(new_value, 1, 0)), text_value))**

**➡️** This command will define a new function named **MultiReplace** as well as variables with **text_value**,** old_value **and** new_value**.

**👉 OFFSET(old_value, 1, 0)**

**➡️** That will give you data which you want to replace.

**👉 OFFSET(new_value, 1, 0)**

**➡️ **This part of the code is for the data which are gonna replace other data.

**👉 IF(old_value<>””, MultiReplace(SUBSTITUTE(text_value, old_value, new_value), OFFSET(old_value, 1, 0), OFFSET(new_value, 1, 0)), text_value))**

**➡️** Here we are telling what to do if our selected **text_value** matches with our **old_value** data which is replacing with the **new_value**. Also what to do if nothing matches which is keeping the **text_value **intake.

**👉 MultiReplace(B6:B12,$D$6,$E$6)**

**➡️** It’s the newly defined function here we are dealing with. The first part **B6:B12 **is our range dataset. **$D$6 **refers to **old_value **and **$E$6** refers to **new_value**.

**Out-turn:** NY, New Jersey

**📕 Read More: ****3 Easy Ways to Find and Replace in Multiple Excel Files**

**Method 5**

### 5. With Replace Function

The **Replace Function** is an easy approach for replacing specific values in cells. By this function, we can replace by denoting specifically what to replace by its position in the data and also what is the total number of that character which will be replaced later, in a dataset.

We have here a dataset that we are gonna make smaller by replacing 2010 to 10.

⬇️⬇️ **STEPS **⬇️⬇️

- Select the cell in which we are gonna get the result.
- After that type in the following formula.

**=REPLACE(B6,1,4,10)**

- Here we can see that 2010 is replaced with 10.

- Now use the Fill Tool to
**Copy along the column**.

**Method 6**

### 6. Combination of VLOOKUP And IFNA Functions

**VLOOKUP Function** is handy for finding certain values in a worksheet. You can use this function by following the format down below

**=VLOOKUP(What to look up, where to look for it ( The Range ), which column in the range has the value which you want to show, and whether to return an approximate or exact match, denoted as 1/TRUE or 0/FALSE)**

Here in this example we have 7 short names for States. We want to abbreviate only 4 of them by replacing values. So we will be using a combination of **IFNA** and **VLOOKUP** Functions to get our result

⬇️⬇️ **STEPS **⬇️⬇️

- We are choosing the cell in which we want the answer.

- After that Copy the formula down below which is the combined formula.

**=IFNA(VLOOKUP($B6,D6:E9, 2, FALSE), B6)**

- You will have the following result by pressing
**Enter**.

- Now
**Copy the Formula**along the column and then you’ll find all the outputs.

**🔨 ****Formula Breakdown**

**👉 VLOOKUP($B6,D6:E9, 2, FALSE)**

**➡️** The first part of the **VLOOKUP** function **$B6 **says what we want look for here, next section defines where to look for it which is **D6:E9**. Then comes which column in the range has the value which you want to show which is **2 **for us, lastly **FALSE** for **EXACT MATCH**.

**👉 IFNA(VLOOKUP($B6,D6:E9, 2, FALSE), B6)**

**➡️** The **IFNA **decides what to show if no value is matched which is **B6**.

**Out-turn:** New Jersey.

**Method 7**

### 7. Using VBA Code

We are going to use a VBA code to solve our problem of changing multiple values by replacing them with a specific value. The text contents within Column B of the underlying dataset will now be changed by swapping out the initial numerical values. The table on the right side contains both the old values that you will replace and also, the new values.

⬇️⬇️ **STEPS **⬇️⬇️

- First, go to the bottom left side and then
**right-click**on the top of the sheet name you are working on and click on the**View Code**.

- Now a new window will open up. Copy the following VBA code there.

```
Option Explicit
Sub FindnReplaceMultipleValues()
Dim RngVal As Range
Dim InputText As Range
Dim OutputText As Range
On Error Resume Next
Set InputText = Application.InputBox("Range of Replacable Data:", "Finding And Replacing Multiple Values", Application.Selection.Address, Type:=8)
Err.Clear
If Not InputText Is Nothing Then
Set OutputText = Application.InputBox("Replace Data:", "Finding And Replacing Multiple Values", Type:=8)
Err.Clear
If Not OutputText Is Nothing Then
Application.ScreenUpdating = False
For Each RngVal In OutputText.Columns(1).Cells
InputText.Replace what:=RngVal.Value, replacement:=RngVal.Offset(0, 1).Value
Next
Application.ScreenUpdating = True
End If
End If
End Sub
```

- Now hit A new window that will be created like the following picture which wants you to state cells or ranges which you want to change or replace. We are selecting
**$B$6:$B$12**for column**B**. Select**OK**for confirming.

- Now we have a new window that says
**Find And Replace Multiple Values**so we have to let the code know what to find and what to replace. So we are selecting the table by which we want the change. So we are selecting**$D$6:$E$9**. Then**OK**for confirmation.

- You are done and then you may see the following results if you have done everything properly.

## 📄 Important Notes

`🖊️`

Be careful while using the code as there are a lot of terms and if you lose concentration the code may be incorrect.

`🖊️`

You can open the VBA window in a lot of different ways and look at what’s below for instance.

`🖊️`

While writing formulas carefully see the suggestions and don’t forget the commas and parentheses.

`🖊️`

Use ”**$**” for absolute value.

## 📝 Takeaways from This Article

`📌`

Combining **XLOOKUP, VLOOKUP, REPLACE, IFNA, LAMBDA, SUBSTITUTE** functions and then how to write formulas.

`📌`

How to write a **VBA** code.

`📌`

The **Find and Select Tool **and consequently it’s usage.

`📌`

Finding and Replacing multiple values in Excel.

`📌`

Different ways of selecting the **Macros **option for **VBA**.

**Conclusion**

I hope you were able to use the techniques I demonstrated in this Excel lesson to find and replace multiple values. Therefore, you can see, there are a lot of options on how to do this. Decide deliberately on the approach that best addresses your circumstance. In addition I advise repeating the steps if you become confused in any of the steps if you get stuck. Practice on your own after taking a look at find and replace multiple values Excel file in the practice workbook as I’ve provided above because practice makes a man perfect. Therefore, I earnestly hope you can use it. Please share your experience with us in comments about how your experience was throughout the article and what we need to improve or add to make things better for you folks. However, if you have any problem regarding Excel spit it in comment box. The **Excelden **crew is always available to answer your questions. In conclusion, keep educating yourself by reading. For more Excel-related articles please visit our website **Excelden.com**.