PromptOperations

Prompt: get the Excel formulas that compare two sheets (and test them)

Ask the AI for a status column (REMOVED, CHANGED, SAME), an ADDED flag and a highlight rule, where a missing row can never come back as 0. The real answer, tested in Excel on eight tricky rows.

Works with: Excel, ChatGPT, Claude, Copilot

The task

Compare two big exports inside Excel, by key, with a status column and a highlight, instead of pasting thousands of rows into a chat.

Prompt

I use Excel with English function names and commas between arguments. My workbook has two sheets, Monday and Friday. On both, column A is the SKU (text), column B the item name and column C the quantity, with headers in row 1 and data in rows 2 to 9.

Write:
1. A formula for Monday!D2, to fill down, that returns REMOVED if the SKU is not on the Friday sheet, CHANGED if the quantity is different, and SAME otherwise. Match the SKU exactly. A quantity of 0 is a real value: a missing SKU must never come back as 0.
2. A formula for Friday!D2, to fill down, that returns ADDED if the SKU is not on the Monday sheet.
3. A conditional formatting formula that highlights the whole row on the Monday sheet when D is not SAME.
4. A short test table: for each kind of row (same, changed, changed to 0, removed with 0 stock, added), the result I should see.
Keep the formulas as short as you can.

How to use it

Change the sheet names, columns and row range to match your workbook.
Paste the formulas the AI gives you in D2 and fill them down.
Add four test rows: a quantity that goes to 0, a row that disappears with 0 stock, a new row, a code that lost its leading zero.
Check each status by hand once before you trust it. Our test: How to compare two Excel sheets.

What you get

A formula that returns REMOVED for a missing row (never 0), CHANGED for a quantity that goes to 0, SAME otherwise; an ADDED flag on the other sheet; and a conditional formatting rule such as =$D2<>"SAME".

Example answer we got

**1. Monday!D2** (fill down to D9)

```
=IFNA(IF(XLOOKUP(A2,Friday!A$2:A$9,Friday!C$2:C$9)=C2,"SAME","CHANGED"),"REMOVED")
```

- **Exact SKU match:** XLOOKUP looks for an exact match by default. VLOOKUP, MATCH and COUNTIF treat `*` and `?` in a SKU as wildcards, but XLOOKUP doesn't.
- **A missing SKU is never 0:** XLOOKUP returns #N/A. The comparison and the IF pass the #N/A through unchanged, and only IFNA turns it into REMOVED. A real 0 on Friday comes back as 0 and is compared like any other number.
- **Upper and lower case:** XLOOKUP ignores case, so ab-01 matches AB-01. If case matters for your SKUs, replace `A2,Friday!A$2:A$9` with `TRUE,EXACT(A2,Friday!A$2:A$9)`.
- **Excel version:** XLOOKUP and XMATCH need Microsoft 365 or Excel 2021 or later.

**2. Friday!D2** (fill down to D9)

```
=IF(ISNA(XMATCH(A2,Monday!A$2:A$9)),"ADDED","")
```

XMATCH also uses an exact match with no wildcards. To make it case-sensitive, use `XMATCH(TRUE,EXACT(A2,Monday!A$2:A$9))` instead.

**3. Conditional formatting (Monday sheet)**

Select **A2:D9**, with A2 as the active cell. Then go to Home › Conditional Formatting › New Rule › "Use a formula to determine which cells to format", and enter:

```
=$D2<>"SAME"
```

The `$` fixes the column to D, so every cell in a row checks that row's status. Apply the rule only to rows 2–9. Empty rows have a blank D, which also counts as "not SAME", so they would be highlighted too.

**4. Test table**

| Case | Monday (A / C) | Friday (A / C) | Monday!D | Friday!D | Monday row highlighted? |
|---|---|---|---|---|---|
| Same | A100 / 5 | A100 / 5 | SAME | *(blank)* | No |
| Changed | A200 / 8 | A200 / 3 | CHANGED | *(blank)* | Yes |
| Changed to 0 | A300 / 4 | A300 / 0 | CHANGED | *(blank)* | Yes |
| Removed, 0 stock | A400 / 0 | *(not there)* | REMOVED | — | Yes |
| Added | *(not there)* | A500 / 6 | — | ADDED | — |

The two rows with 0 catch the common mistakes:
- **Changed to 0** fails if a 0 is treated as "not found".
- **Removed, 0 stock** wrongly shows SAME if a missing SKU is turned into 0, for example with `IFERROR(...,0)`.

Where should you start with AI?

The free assessment gives you, in 4 minutes, the 3 tasks to automate first in your business.

Take the free assessment

The task

How to use it

What you get

Go further

Where should you start with AI?

The essentials of AI for small businesses, once a week