# 7 Ways to Find and Replace Multiple Values in Excel

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.

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

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.  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 ⬇️⬇️

• 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.  🔨 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

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

`📌`  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  