Compare rows between data sets - Exact, Match, Sumproduct, other?

NewUser2

Board Regular
Joined
Jan 27, 2015
Messages
54
Hi, I receive a data set every month and want to verify if the rows in the new data (one row at a time, so by starting at the top say A2:X2), match with a row in the previous data. However, the tables wont be the same length each month, so I can't just select the respective row in the prior months data, I need to select the whole table (which would be on another tab, range: A:X).

This is easy to do with a basic: =COUNTIFS('old_data'!A:A,A2,'old_data'!B:B,B2,'old_data'!C:C,C2))

Or the several other options out there that involve naming each column and each individual cell.

However, my rows extend pretty far so I want to avoid this route. I'm not just lazy (I've already made this using the method explained above), but I don't like being stumped and not finding the answer to something that seems like it should be easy!

Does anyone have an answer to this? I figured maybe some sort of combination of an array lookup with the Exact formula or something like that...

Thanks in advance!
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

Watch MrExcel Video

Forum statistics

Threads
1,129,752
Messages
5,638,162
Members
417,011
Latest member
Amaden95

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top