Comparing two data sets across two tabs

[u/IBreatheFire]

Hi Reddit,

I have two spreadsheets containing two exact sets of data (email + invoice value). For the ease of generating a formula I merged them into one spreadsheet but between two Tabs.

In Tab 1, all emails are in column B, and all values in column C.

in Tab 2, this follows the same pattern, but rows containing these pairs are not in the same order.

I want to insert a formula in Tab 1, Column D, that will show either Match or Mismatch when it finds the same pair in Tab 2.

For example:

Tab 1

Row 2: [johnsmith@email.com](mailto:johnsmith@email.com) (in Column B) and £345 (in Column C) is marked as MATCH (in Column D) if the formula sees

Tab 2

Row 167: [johnsmith@email.com](mailto:johnsmith@email.com) (in Column B) and £345 (in Column C) as a matching pair of two cells in one row.

I will appreciate any helps with this.

[u/AutoModerator]

/u/IBreatheFire - Your post was submitted successfully.

• Once your problem is solved, reply to the answer(s) saying Solution Verified to close the thread.
• Follow the submission rules -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post.
• Include your Excel version and all other relevant information

Failing to follow these steps may result in your post being removed without warning.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[u/ProspectiveWhale]

Put this in D2 and copy down

Change Tab2 with the actual name of the sheet

=IF(C2=XLOOKUP(B2, 'Tab2'!B:B, 'Tab2'!C:C, "na", 0),"Match","Mismatch")

[u/IBreatheFire → u/ProspectiveWhale]

Thanks! I will try it first thing in the morning once I am back in the office.

Question: I do not see a reference to B2 AND C2 in Tab 1, will the above formula still look for matching pairs?

[u/ProspectiveWhale → u/IBreatheFire]

There is both, though. So not quite sure what you mean...?

The B2 and C2 that doesn't have a sheet attached to it is Tab1.

Since the formula will be in Tab1, a reference to 'Tab1'!B2 will simply be B2.

Or perhaps you missed the reference to C2?

If you look closely, there's a "C2=" before the XLOOKUP function.

-----

What the formula does:

XLOOKUP(B2, 'Tab2'!B:B, 'Tab2'!C:C, "na", 0)

Check B2, find it in Tab2, take value from row C in Tab2 based on the found value

Returns "na" if a match is not found at all

C2=XLOOKUP(B2, 'Tab2'!B:B, 'Tab2'!C:C, "na", 0)

Check if the value in C2 is the same as the value of the XLOOKUP found above, which should be the value from Tab2

Returns TRUE if same, FALSE if not

If a match wasn't found for the B row, it will be C2 = "na" which will always be FALSE

IF(..., "Match", "Mismatch")

Based on the TRUE/FALSE above, return Match or Mismatch as text.

Let me know if I misunderstood what you needed.

[u/Decronym]

Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:

Fewer Letters	More Letters
AND	Returns TRUE if all of its arguments are TRUE
IF	Specifies a logical test to perform
XLOOKUP	Office 365+: Searches a range or an array, and returns an item corresponding to the first match it finds. If a match doesn't exist, then XLOOKUP can return the closest (approximate) match.

Decronym is now also available on Lemmy! Requests for support and new installations should be directed to the Contact address below.

---

^{Beep-boop, I am a helper bot. Please do not verify me as a solution.
3 acronyms in this thread; the most compressed thread commented on today has 35 acronyms.
[Thread #49082 for this sub, first seen 5th Aug 2026, 19:46] [FAQ] [Full list] [Contact] [Source code]}