Find Text in an Excel Column and Replace It with a Related Value
Question details
Find a lookup value within text in one column and replace it with the corresponding value from another column.

- 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.
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.
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.
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.
Press Enter. In older Excel versions on Windows, confirm the array formula with Ctrl+Shift+Enter instead.
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.
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.

Look Up an Exact Value and Keep the Existing Result
Use this alternative when column A must exactly match a value in column B and column D already contains a fallback value.
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. Open the workbook: Open WPS Spreadsheets and use Open to select your workbook.
- 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. 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. 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.

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.




