Data Import & Export

How to Compare Two Excel Sheets and Mark Matching Assets

Bushra ParveenBushra Parveen Nov 11, 2026 868 views

Question details

The user needs to compare asset lists located on two separate sheets (Webfinity and CSOD) and assign specific text labels based on whether an asset is present in both sheets, only in Webfinity, or only in CSOD.

How to Compare Two Excel Databases and Mark Matching Assets
Product
Spreadsheet
Device & OS
not provided
Scenario
Reconciling two database sheets and explicitly marking the matching and unmatched items with custom labels.
Observed behavior
The user needs a reliable formula or automation method to evaluate and output the exact status ('Both', 'Webfinity Only', 'CSOD Only') for each asset record.
Before you start

Ensure that the unique identifiers (like Asset IDs) in both the Webfinity and CSOD sheets are formatted identically, without hidden trailing spaces or mismatched data types, so the comparison formulas work accurately.

Solution 1Recommended

Use Nested IF and COUNTIF Formulas to Label Records

This is the most accurate method to evaluate multiple sheets and output a customized status label for matching and non-matching records.

By combining the IF statement with COUNTIF, you can check whether a specific asset ID exists in a target sheet. Nesting these formulas allows you to evaluate conditions for both sheets simultaneously.

1
Create a Master List

Consolidate all unique Asset IDs from both the Webfinity and CSOD sheets into Column A of a new sheet called 'Master Comparison'.

2
Set Up the Status Column

Click on cell B2 (next to your first Asset ID) and prepare to enter the comparison formula.

3
Enter the Formula

Type the formula: =IF(AND(COUNTIF(Webfinity!A:A, A2)>0, COUNTIF(CSOD!A:A, A2)>0), "Both", IF(COUNTIF(Webfinity!A:A, A2)>0, "Webfinity Only", "CSOD Only")) and press Enter.

4
Apply Formula to All Rows

Select cell B2, then hover over the bottom-right corner until the cursor turns into a cross. Double-click or drag down to apply the formula to the rest of the asset list.

Use Nested IF and COUNTIF Formulas to Label Records
Data Accuracy: If you are comparing datasets directly on the Webfinity sheet instead of a master list, you can simplify the formula to check only the CSOD sheet: =IF(COUNTIF(CSOD!A:A, A2)>0, "Both", "Webfinity Only").

Compare and Reconcile Excel Databases Easily with WPS Spreadsheet

WPS Spreadsheet fully supports advanced comparison formulas like IF, COUNTIF, and VLOOKUP. Its familiar interface makes it incredibly easy to reconcile asset lists, handle large datasets, and analyze discrepancies without lag.

  1. 1. Open Your Workbooks: Launch WPS Spreadsheet and open the file containing your Webfinity and CSOD sheets.
  2. 2. Select the Target Cell: Click on the cell where you want the 'Both', 'Webfinity Only', or 'CSOD Only' label to appear.
  3. 3. Input the Comparison Formula: Type the nested IF/COUNTIF formula and press Enter to instantly evaluate the asset record.
  4. 4. Fill Down the Column: Double-click the fill handle in the bottom-right corner of the cell to automatically label thousands of rows in seconds.
100% compatible with Microsoft Excel formulas and .xlsx formatsLightweight performance handles large database comparisons smoothlyBuilt-in duplicate removal and data validation toolsFree alternative equipped with advanced data analysis features
microsoft office alternative - wps office

Frequently Asked Questions

Why is my COUNTIF formula returning incorrect labels even when the asset IDs look identical?

This usually happens due to invisible trailing spaces, hidden characters, or mismatched data types (such as numbers stored as text). Use the TRIM function or the 'Text to Columns' feature in the Data tab to clean your unique identifiers before comparing.

Can I highlight the matching rows instead of just assigning text labels?

Yes. You can use Conditional Formatting for visual cues. Select your data range, navigate to 'Home' > 'Conditional Formatting' > 'New Rule', choose 'Use a formula to determine which cells to format', and enter your COUNTIF formula to apply a background fill color.

Is there a way to automate this comparison if my database sheets update frequently?

For frequently updated databases, it is highly recommended to format your data ranges as Tables (press Ctrl+T). When you add new assets, formulas referring to the table columns will automatically expand and recalculate.