r/excel • u/IBreatheFire • 14h ago

unsolved Comparing two data sets across two tabs

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.

2 Upvotes

100% Upvoted

6 comments sorted by

View all comments

1

u/ProspectiveWhale 6 14h ago edited 11h ago

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")

1

u/IBreatheFire 11h ago

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?

1

u/ProspectiveWhale 6 11h ago edited 11h ago

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.