How to Compare Two Excel Sheets and Mark Matching Assets
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.

- 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.
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.
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.
Consolidate all unique Asset IDs from both the Webfinity and CSOD sheets into Column A of a new sheet called 'Master Comparison'.
Click on cell B2 (next to your first Asset ID) and prepare to enter the comparison 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.
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.

Identify Unmatched Records Using VLOOKUP
If you prefer to pull in related data while identifying matches, VLOOKUP combined with IFNA is a great alternative.
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. Open Your Workbooks: Launch WPS Spreadsheet and open the file containing your Webfinity and CSOD sheets.
- 2. Select the Target Cell: Click on the cell where you want the 'Both', 'Webfinity Only', or 'CSOD Only' label to appear.
- 3. Input the Comparison Formula: Type the nested IF/COUNTIF formula and press Enter to instantly evaluate the asset record.
- 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.

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.




