Excel Formula to Search Text in Two Cells and Return a Matching Value
Question details
The user needs an Excel formula to search sequentially through two cells (C1, then C2) for text matching a predefined list (D1:D10) and return a corresponding result from another list (E1:E10).
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Looking up matching substrings from a list within target cells that may contain multiple lines or paragraphs of text, and returning a mapped value or 'Not Found'.
- Observed behavior
- Standard lookup formulas fail to sequentially evaluate multiple cells or struggle to identify text substrings when the target cell contains paragraphs with line breaks.
Ensure your search list range (D1:D10) does not contain empty cells, hidden spaces, or unintended wildcard characters (*, ?, ~), as these can cause inaccurate matching within paragraphs.
Use an INDEX, MATCH, and SEARCH Array Formula
This solution sequentially searches C1 and then C2 for any substring listed in D1:D10, bypassing line break issues, and returns the adjacent value from E1:E10.
By combining IFERROR, INDEX, MATCH, and ISNUMBER(SEARCH), Excel can effectively look for an array of text strings inside a single cell. The SEARCH function is case-insensitive and inherently ignores line breaks, making it perfect for multi-line paragraphs.
Nesting two IFERROR statements allows the formula to check C1 first, move to C2 if C1 yields no match, and ultimately return 'Not Found' if neither cell contains the target text.
Ensure the keywords you want to find are listed in column D (e.g., D1:D10) and the values you want returned are in column E (e.g., E1:E10).
Click on the cell where you want the final matching value to be displayed.
Type the following formula: =IFERROR(INDEX($E$1:$E$10, MATCH(TRUE, ISNUMBER(SEARCH($D$1:$D$10, C1)), 0)), IFERROR(INDEX($E$1:$E$10, MATCH(TRUE, ISNUMBER(SEARCH($D$1:$D$10, C2)), 0)), "Not Found"))
If you are using Excel 2019 or earlier, do not just press Enter. Instead, press Ctrl + Shift + Enter to evaluate it as an array formula. Curly braces {} will appear around the formula. In Microsoft 365, pressing Enter normally is sufficient.
Process Multi-line Cell Searches Easily with WPS Spreadsheet
WPS Spreadsheet fully supports complex array functions like INDEX, MATCH, and SEARCH out of the box. You can effortlessly search through paragraphs in multiple cells without worrying about compatibility issues.
- 1. Open your workbook in WPS Spreadsheet: Launch WPS Office, select 'Spreadsheet', and open your data file containing the multi-line cells.
- 2. Enter the nested lookup formula: Select your output cell and input the nested IFERROR and INDEX/MATCH combination to check C1 and C2 against your data ranges.
- 3. Calculate the array: Press Ctrl+Shift+Enter. WPS Spreadsheet perfectly renders array calculations, instantly parsing the multi-line text and returning the correct matched value.
Frequently Asked Questions
Why does the formula fail when searching text that contains line breaks?
If you are using exact match functions (like VLOOKUP), line breaks (character 10) alter the string. By using the SEARCH function instead, the formula looks for a substring regardless of surrounding line breaks or paragraph formatting within the cell.
Can I expand this formula to search a third cell (C3)?
Yes. You can add another layer to the IFERROR chain. Replace the 'Not Found' argument with another IFERROR(INDEX(...)) block checking C3, and place 'Not Found' at the very end of the new chain.
What happens if a cell matches multiple values from the list?
The MATCH function returns the position of the first condition that evaluates to TRUE. Therefore, it will return the value corresponding to the first matched keyword it encounters in your D1:D10 list, ordered from top to bottom.
Why is my formula returning an error instead of 'Not Found'?
This usually happens in older versions of Excel if you press 'Enter' instead of 'Ctrl + Shift + Enter'. Without the array command, the formula cannot process the search array against a single cell, resulting in a #VALUE! or #N/A error.
