Function Problems

Find Text in an Excel Column and Replace It with a Related Value

Steven ParkerSteven Parker Nov 18, 2026 869 views

Question details

Find a lookup value within text in one column and replace it with the corresponding value from another column.

Find Text in an Excel Column and Replace It with a Related Value
Product
Microsoft Excel
Device & OS
not provided
Scenario
Column A contains the original text, column B contains values to find, and column C contains their replacements.
Observed behavior
The user wants a formula to show the modified text in a separate column and preserve the original text when no lookup value matches.
Before you start

Arrange the values to find in column B and their corresponding replacements in column C, starting in row 2. Use a contiguous lookup range without blank cells in column B, and ensure the text to find has the same capitalization as it appears in column A.

Solution 1Recommended

Replace Matching Text with an INDEX and MATCH Formula

Use this method when a value in column B appears within the text in column A and its replacement is in the same row of column C.

The formula finds the first matching entry in the lookup range and uses its related value as the replacement. SUBSTITUTE replaces all occurrences of that entry in the original text.

SEARCH is case-insensitive and recognizes wildcard characters, while SUBSTITUTE is case-sensitive. For predictable results, use matching capitalization and avoid wildcard characters in the lookup values.

1
Enter the formula

Select D2 and enter: =IFERROR(SUBSTITUTE(A2,INDEX($B$2:$B$100,MATCH(TRUE,ISNUMBER(SEARCH($B$2:$B$100,A2)),0)),INDEX($C$2:$C$100,MATCH(TRUE,ISNUMBER(SEARCH($B$2:$B$100,A2)),0))),A2). Change both lookup ranges to match your data, keeping the B and C ranges the same size.

2
Confirm the formula

Press Enter. In older Excel versions on Windows, confirm the array formula with Ctrl+Shift+Enter instead.

3
Fill the results down

Drag the fill handle from D2 down alongside the entries in column A. If there is no match, the formula displays the original text from column A.

4
Replace the originals if needed

To overwrite the original cells after checking the results, copy the cells in column D, select the corresponding cells in column A, and use Home > Paste > Values.

Replace Matching Text with an INDEX and MATCH Formula
A formula does not overwrite its source: The formula displays its result in column D. Paste the results as values into column A only if you want to replace the original text.
Work with text in WPS Spreadsheets

Replace Matched Text with WPS Spreadsheets

Use a lookup formula in WPS Spreadsheets to find text from a lookup column and return the related replacement in a separate column.

  1. 1. Open the workbook: Open WPS Spreadsheets and use Open to select your workbook.
  2. 2. Check the lookup columns: Place the original text in column A, the values to find in column B, and their replacements in column C. Keep the lookup cells in column B nonblank.
  3. 3. Enter the formula: Select D2 and enter =IFERROR(SUBSTITUTE(A2,INDEX($B$2:$B$100,MATCH(TRUE,ISNUMBER(SEARCH($B$2:$B$100,A2)),0)),INDEX($C$2:$C$100,MATCH(TRUE,ISNUMBER(SEARCH($B$2:$B$100,A2)),0))),A2). Adjust the lookup ranges to fit your data.
  4. 4. Confirm and fill down: Press Enter, then drag D2's fill handle down. If your version requires array formula entry, confirm the formula with Ctrl+Shift+Enter on Windows before filling it down.
Find and replace text using related values in a lookup tableOpen and edit Microsoft Excel XLSX and XLS filesFill formulas down a column for multiple rowsUse a familiar spreadsheet interface
microsoft office alternative - wps office

Frequently Asked Questions

What happens if no lookup value is found?

The IFERROR part of the formula returns the original text from A2 when the lookup produces an error.

Which value is used if more than one lookup entry matches?

MATCH selects the first matching entry in the column B lookup range, and INDEX returns the corresponding replacement from column C.

Why was matching text not replaced?

SEARCH ignores capitalization, but SUBSTITUTE requires matching capitalization. Check that the value in column B matches the case of the text in column A and that the lookup range has no blank cells.

Can I replace the original cells instead of using another column?

A formula must return its result in another cell. After checking column D, copy its results and use Home > Paste > Values on the corresponding cells in column A.

How do I match the entire cell instead of part of its text?

Use an exact-match lookup such as =IFERROR(VLOOKUP(A2,$B$2:$C$100,2,FALSE),D2) in E2. It returns the related value from column C or the fallback value in D2.