How to Use Conditional Formatting for Partial Text Matches Across Sheets in Spreadsheet
Question details
Highlight matching values on two different worksheets when one list contains a short text value and the other contains that same value followed by additional text.
- Product
- Spreadsheet
- Device & OS
- not provided
- Scenario
- Applying advanced conditional formatting rules to identify and highlight cells that partially match text strings across different data ranges, including scenarios involving multiple columns.
- Observed behavior
- Standard conditional formatting rules or single-column array formulas fail when the search criteria expand to an array of multiple columns, requiring a multi-range or wildcard formula approach.
Ensure that your data ranges are clearly defined and that both lists are located within the same workbook, as conditional formatting requires accessible references to apply cross-sheet formulas.
Highlight the Base List (Matching Against Scan Results)
Use this solution to highlight cells in your primary list that match the beginning of text entries in a secondary scan column.
By combining the AND, OR, and LEFT functions, you can check if a fixed number of characters in your base list corresponds to the beginning of any value in your target range.
Highlight the range you want to format. For example, select A2:A29, ensuring A2 is the active cell.
Navigate to the Home tab, click on 'Conditional Formatting', and select 'New Rule'. Choose 'Use a formula to determine which cells to format'.
In the formula box, type: =AND(A2<>"",OR(LEFT(A2,3)=$D$2:$D$1000)). This assumes you are matching the first 3 characters against column D.
Click 'Format', select your preferred highlight color under the Fill tab, and click 'OK' to apply the rule.
Highlight Scan Results (Matching Against the Base List)
Use this method if you need to highlight the longer text strings that contain the shorter base values.
Search Across Multiple Columns
Fix formula errors when your search range spans across multiple columns (e.g., D:G instead of just D).
Effortlessly Manage Advanced Conditional Formatting in WPS Spreadsheet
WPS Spreadsheet provides robust support for complex formulas, array calculations, and cross-sheet conditional formatting. You can easily highlight partial matches, duplicates, and custom data rules while ensuring full compatibility with standard spreadsheet formats.
- 1. Open your workbook: Launch WPS Spreadsheet and open the document containing the datasets you want to compare.
- 2. Access Conditional Formatting: Highlight your target cells, navigate to the 'Home' tab, and click on 'Conditional Formatting'.
- 3. Apply your custom formula: Select 'New Rule', choose the formula option, paste your COUNTIF or SUMPRODUCT formula, and define your highlight colors.
Frequently Asked Questions
Why is my conditional formatting formula not working across an array of multiple columns?
When you expand a search range from a single column (e.g., $D$2:$D$1000) to an array of multiple columns (e.g., $D$2:$G$1000), single-dimension functions or exact matches may fail. Instead, use an array-compatible function like COUNTIF($D$2:$G$1000, "*"&A2&"*")>0 to evaluate the entire range properly.
Can I use wildcards for partial text matches in conditional formatting?
Yes, wildcards like the asterisk (*) or question mark (?) can be appended to cell references using the ampersand (&) inside functions like COUNTIF or SEARCH. For example, COUNTIF(Range, A1&"*") looks for any cell that begins with the text found in A1.
How do I highlight cells based on data located in another worksheet?
You can reference another worksheet directly in your conditional formatting formula by using the proper sheet name syntax. Ensure you use absolute references where necessary, for example: =COUNTIF(Sheet2!$A$2:$A$1000, "*"&A2&"*")>0.
