How to match two sheets: Google Sheets & Excel guide

If you run a sales, marketing or operations team, you already know the pain: two spreadsheets that should match, but do not. A lead list from last week versus the CRM export today. An invoice register versus bank payouts. Matching the data across worksheets in Google Sheets and Excel is where hours quietly disappear.

Below is a practical playbook: first the classic manual methods, then no‑code automation, and finally how an AI agent can own this entire workflow at scale.

‍

1. Manual methods in Google Sheets and Excel

‍

1.1 Match by key with VLOOKUP or XLOOKUP

The most common case is: both sheets share a unique key (email, ID, invoice number).

In Google Sheets with VLOOKUP

1. Open your primary sheet (Sheet A) and the secondary sheet (Sheet B) in the same file, or use IMPORTRANGE if B is in another file.
2. In Sheet A, insert a new column called Match result.
3. In the first row of that column, use:
   =IFERROR(VLOOKUP(A2, SheetB!$A$2:$D$10000, 2, FALSE), "Not found")
   • A2 is the key in Sheet A.
   • SheetB!$A$2:$D$10000 is the range in Sheet B where column A is the key and column B (index 2) is what you want to pull.
4. Fill down. Any #N/A or custom text like Not found marks a missing match.

Docs: https://support.google.com/docs/answer/3093318

‍

In Excel with XLOOKUP (modern)

1. Open the workbook that has both sheets.
2. In Sheet A, add a Match status column.
3. Use:
   =IFERROR(XLOOKUP(A2, SheetB!$A:$A, SheetB!$B:$B), "Not found")
4. Copy down. XLOOKUP is more flexible than VLOOKUP and does not require sorting.

Docs: https://support.microsoft.com/en-us/office/xlookup-function-78a5dcd4-2a7b-43d4-9020-17b20284d5d8

‍

1.2 Use INDEX + MATCH for flexible matching

INDEX + MATCH is powerful when your lookup column is not the leftmost.

Google Sheets and Excel (same idea)

1. In Sheet A, pick the cell where you want the value from Sheet B.
2. Use:
   =IFERROR(INDEX(SheetB!$D:$D, MATCH(A2, SheetB!$A:$A, 0)), "Not found")
   • MATCH finds the row where the key in A2 appears in Sheet B column A.
   • INDEX returns the value from column D on that row.
3. Fill down and filter on Not found to see mismatches.

Docs Sheets: https://support.google.com/docs/answer/3098242 and https://support.google.com/docs/answer/3093378
Docs Excel: https://support.microsoft.com/en-us/office/index-function-a5dcf0dd-996d-40a4-a822-b56b061328bd and https://support.microsoft.com/en-us/office/match-function-e8dffd45-c762-47d6-bf89-533f4a37673a

‍

1.3 Compare whole or partial rows

Sometimes you want to know whether entire rows match across sheets (for example, all customer fields except notes).

Exact row compare in Excel

1. In Sheet1, suppose row 2 should match row 27 in Sheet2.
2. In a helper cell, use:
   =AND(Sheet1!B2:E2 = Sheet2!B27:E27)
3. If you need to compare many rows, combine with MATCH:
   =AND(Sheet1!B2:E2 = INDEX(Sheet2!B:E, MATCH(Sheet1!A2, Sheet2!A:A, 0), 0))
   This finds the correct row by key, then checks the key fields.

Partial row compare
Use AND on specific columns only, such as name, phone, email, address:
=AND(Sheet1!B2=Sheet2!B27, Sheet1!C2=Sheet2!C27, Sheet1!D2=Sheet2!D27, Sheet1!E2=Sheet2!E27)

‍

1.4 Highlight differences with conditional formatting

In Google Sheets

1. Select the range you want to compare in Sheet A, for example A2:D5000.
2. Format → Conditional formatting → Custom formula.
3. Use a formula like:
   =A2 <> INDIRECT("SheetB!" & ADDRESS(ROW(), COLUMN()))
4. Choose a fill color. Now any cell that differs from the same cell on Sheet B is highlighted.

Docs: https://support.google.com/docs/answer/78413

‍

In Excel

1. Select A2:D5000 on Sheet1.
2. Home → Conditional Formatting → New Rule → Use a formula.
3. Use:
   =A2 <> INDIRECT("Sheet2!" & ADDRESS(ROW(), COLUMN()))
4. Pick a color and apply.

Docs: https://support.microsoft.com/en-us/office/use-conditional-formatting-cbfd4d28-4b47-4c9b-be2c-8f167fe73c2a

‍

1.5 Use Excel Spreadsheet Compare for full workbooks

For mission‑critical Excel models, Spreadsheet Compare gives you a professional diff view.

1. Make sure you have Microsoft 365 Apps for enterprise or Office Professional Plus.
2. Open Spreadsheet Compare from the Start menu.
3. Click Home → Compare Files.
4. Select the older workbook under Compare and the newer under To.
5. Choose options such as Formulas, Macros, Cell Format, then OK.
6. Review the two‑pane grid with color‑coded changes and the detailed pane below.

Docs: https://support.microsoft.com/en-us/office/basic-tasks-in-spreadsheet-compare-f2b20af8-a6d3-4780-8011-f15b3229f5d8

This is excellent for auditing quarterly finance models or complex pricing sheets.

‍

2. No-code automation with workflow tools

Manual steps still rely on you opening files and pressing buttons. No‑code tools can at least standardize when and how comparisons happen.

‍

2.1 Use Google Sheets plus a no‑code integration

Imagine a marketer who exports leads from a CRM daily and wants to know which contacts are new versus yesterday.

1. Use a tool like Zapier or Make to:
   • Watch your CRM for new or updated records.
   • Append them to a Google Sheets tab called Raw export.
2. Maintain another tab called Master with the cleaned list.
3. In Master, use VLOOKUP, XLOOKUP (if available) or INDEX MATCH against Raw export to mark new records or changes.
4. The automation keeps both tabs in sync; your formulas keep the matches accurate.

You are not writing code; you are assembling blocks. The heavy lifting still happens inside Sheets formulas.

‍

2.2 Use Power Automate with Excel

For businesses on Microsoft 365, Power Automate can orchestrate matching workflows:

1. Create a flow that triggers when a new file lands in a OneDrive or SharePoint folder, such as Finance exports.
2. The flow opens the new workbook, copies relevant sheets into a standard template workbook or table.
3. Inside that template, you have predefined XLOOKUP or INDEX MATCH formulas that compare the new data against a reference sheet.
4. The flow can then email a summary or save a clean matched report to a shared folder.

No code, just configuration. But you still design and maintain the formulas and mappings.

‍

3. At-scale automation with an AI agent

Once your matching logic works, the most expensive part is still human time: opening files, clicking buttons, fixing small issues, and repeating the process across dozens of workbooks.

This is exactly where a Simular AI agent shines. Simular Pro is a computer‑use agent that can act like a power analyst across your desktop, browser and cloud tools.

‍

3.1 Agent that drives Excel Spreadsheet Compare for you

‍

Workflow:

1. You drop two workbooks (for example, Forecast Q3 old and Forecast Q3 new) into a folder.
2. You trigger the Simular agent via a webhook or schedule.
3. The agent opens Spreadsheet Compare, selects the correct pair of files, sets the comparison options and runs the diff.
4. It exports the changes to an Excel or CSV report, then writes a short human‑readable summary and saves everything in your shared drive or posts to Slack.

‍

Pros:

• Leverages Microsoft s own comparison logic.
• Perfect for regulated finance, healthcare or insurance workflows where an audit trail matters.
• Transparent execution: every click and keystroke is logged by Simular.

‍

Cons:

• Windows and Excel specific.
• Best for batch comparisons rather than ad‑hoc one‑offs.

‍

3.2 Agent that reconciles Google Sheets and Excel side by side

Many agencies live in Google Sheets while finance lives in Excel. A Simular agent can bridge both worlds.

‍

Workflow:

1. Agent opens a Google Sheet in the browser with your master marketing data.
2. It downloads or opens the latest Excel export from a shared drive.
3. In the Sheet, the agent inserts or updates formulas like VLOOKUP or INDEX MATCH to pull in values from the Excel data (using IMPORTDATA or by pasting the export into a helper tab).
4. It runs checks for mismatches, flags rows where spend, revenue or status differ beyond a threshold, and writes a summary tab called Exceptions.

‍

Pros:

• Works across browser and desktop without you building APIs.
• Easy to tweak the logic: you simply adjust formulas or thresholds.

‍

Cons:

• Initial setup requires you to define clear sheet structures.
• Very messy data still needs some human cleanup.

‍

3.3 Agent that scales reconciliation across many files

For enterprises or busy agencies, the real power is running this daily across dozens of clients or business units.

‍

Workflow:

1. You define a naming convention for files: for example, ClientName_Report_Month.xlsx.
2. The Simular agent loops through a folder, pairing this month s file with last month s for each client.
3. For each pair, it runs either Spreadsheet Compare in Excel or formula‑based matching in a template workbook.
4. It compiles a master dashboard, perhaps in Google Sheets, listing for every client: new records, changed metrics, and rows with missing matches.

‍

Pros:

• Turns a multi‑day manual project into a nightly autonomous job.
• Production‑grade reliability: Simular is designed for workflows with thousands to millions of steps.

‍

Cons:

• Requires up‑front design of a repeatable process.
• You still own the business rules; the agent executes them at scale.

When you combine the precision of spreadsheet formulas with an AI computer agent that can click, type and orchestrate across apps, matching data from two worksheets stops being a chore and becomes a dependable background service for your business.