How to Highlight Matching Values Between Columns in Excel & WPS
Question details
The user wants to compare data in one column with another and automatically apply a highlight color to matching entries using conditional formatting.
- Product
- Spreadsheet
- Device & OS
- not provided
- Scenario
- Comparing two lists of data (e.g., names in Column F against Column C) to visually identify and highlight overlapping entries without altering the data structure.
- Observed behavior
- Matching values between the selected columns are automatically highlighted with a designated fill color via a custom formula rule.
Ensure your dataset does not contain unintended trailing spaces, as the matching formula requires exact text or numerical matches to trigger the highlight formatting.
Use Conditional Formatting with the MATCH Formula
This is the most reliable method to dynamically highlight cells in one column that exist anywhere in another specified column.
By combining the ISNUMBER and MATCH functions, you can create a True/False condition. If the MATCH function finds the data in the target column, it returns a position number. ISNUMBER evaluates this to TRUE, triggering the conditional format.
Highlight the cells you want to apply the formatting to. For example, select the range F2:F100. Make sure the first cell (F2) is the active cell.
Navigate to the Home tab on the top ribbon. Click on 'Conditional Formatting', then select 'New Rule' from the dropdown menu.
Choose the option 'Use a formula to determine which cells to format'. In the formula input box, type exactly: =ISNUMBER(MATCH(F2,$C:$C,0))
Click the 'Format' button, go to the 'Fill' tab, and select a highlight color (e.g., yellow or green). Click 'OK' on the format dialog, then 'OK' again to apply the rule.
Highlight Matching Data Seamlessly with WPS Spreadsheet
WPS Spreadsheet fully supports advanced conditional formatting formulas, allowing you to compare lists and highlight duplicates effortlessly. It is an excellent, lightweight tool for all your data analysis needs.
- 1. Open your file: Launch WPS Spreadsheet and open the workbook containing the lists you wish to compare.
- 2. Select the column data: Highlight the cells you want to format, keeping note of the first active cell in your selection.
- 3. Access Conditional Formatting: Go to the Home tab and click 'Conditional Formatting', followed by 'New Rule'.
- 4. Apply the MATCH formula: Select 'Use a formula to determine which cells to format', input your formula (e.g., =ISNUMBER(MATCH(F2,$C:$C,0))), and set your preferred fill color before clicking OK.
Frequently Asked Questions
Can I highlight the differences instead of the matches?
Yes. You can modify the formula to highlight items that do NOT appear in the other column. Use =ISNA(MATCH(F2,$C:$C,0)) in the conditional formatting rule instead.
Why is my conditional formatting highlighting the wrong rows?
This typically occurs if the active cell during your selection doesn't align with the cell referenced in your formula. Ensure that the first parameter in your MATCH function (e.g., F2) is exactly the top-most active cell of your highlighted range.
Does this conditional formatting method work across different worksheets?
Yes, you can reference a column on a completely different sheet. You simply need to include the sheet name in the MATCH formula, such as =ISNUMBER(MATCH(F2,Sheet2!$C:$C,0)).
