Formula Errors

How to Use an Excel Formula to Mark Matching Values in a Column

Maira Mehtab Sep 20, 2026 868 views

Question details

The user needs an Excel formula for Column A that places an 'X' in every row where the corresponding value in Column C matches any value that has already been marked with an 'X' in Column B.

Product: Excel
Device & OS: not provided
Scenario: Marking occurrences of specific matched data across multiple columns.
Observed behavior: The goal is to automatically place an 'X' in Column A for all rows that share a Column C value with a row previously marked in Column B.

Before you start

Ensure your data is organized in continuous columns (A, B, and C) without empty rows interrupting your dataset to guarantee the array formula applies correctly across the range.

Solution 1Recommended

Use IF and COUNTIFS Functions to Mark Matching Values

This method combines the IF and COUNTIFS functions to check if a value in Column C has an 'X' in Column B anywhere in the dataset, and marks Column A accordingly.

The COUNTIFS function is ideal for this scenario because it can count rows that meet multiple criteria across different ranges. By wrapping it in an IF function, we can output a specific character, like 'X', when a match is found in the target array.

Select the target cell

Click on cell A2 (or the first row of your data in Column A) where you want the formula result to appear.

Enter the formula

Type the following formula exactly as shown: =IF(B2="X","X",IF(COUNTIFS(C:C,C2,B:B,"X")>0,"X",""))

Apply the formula to the column

Press Enter to calculate the result. Then, click the small square (fill handle) in the bottom-right corner of cell A2 and drag it down to fill the formula through the rest of the cells in Column A.

Formula Breakdown: The formula first checks if B2 already has an 'X'. If not, it uses COUNTIFS to search the entire Column C for the value in C2 while simultaneously checking if Column B has an 'X' in those matching rows.

Efficient Data Processing in WPS Spreadsheet

Mark Matching Data Easily with WPS Spreadsheet

You can effortlessly apply complex formulas like IF and COUNTIFS to process and analyze large datasets in WPS Spreadsheet. It offers seamless compatibility with Excel formulas and a highly intuitive interface.

1. Open Data in WPS Spreadsheet: Launch WPS Office and open your workbook containing the data in columns A, B, and C.
2. Input the Formula: Select the first cell in your target column (e.g., A2) and enter the combined IF and COUNTIFS formula.
3. Use AutoFill: Double-click the fill handle at the bottom-right of the selected cell to automatically apply the formula to all matching rows.

100% compatible with Microsoft Excel formulas and file formats.Intuitive interface for easy data manipulation and analysis.Lightweight software that handles large datasets smoothly.Free built-in templates and advanced data filtering tools.

microsoft office alternative - wps office

Frequently Asked Questions

Why is my COUNTIFS formula returning a #VALUE error?

A #VALUE error usually occurs if the ranges in your COUNTIFS formula do not match in size. Ensure that you refer to entire columns (like C:C and B:B) or exactly matching specific ranges (like C2:C100 and B2:B100).

Can I mark the cells with a different character or text instead of 'X'?

Yes. You can replace the 'X' in the output part of the formula with any text you prefer. For example, =IF(B2="Yes","Yes",IF(COUNTIFS(C:C,C2,B:B,"Yes")>0,"Yes","")) will output 'Yes' instead.

How do I make the formula ignore blank cells in Column C?

You can add an additional IF statement at the beginning to check for blanks: =IF(ISBLANK(C2),"",IF(...)) so that empty rows are simply skipped and left blank.

Is COUNTIFS case-sensitive when finding matching values?

No, the COUNTIFS function is not case-sensitive. If you need a case-sensitive match, you would need to use an array formula combining the EXACT and SUMPRODUCT functions instead.

Excel & Data Problems