Comparing datasets using match and index functions

Ldoug

New Member
Joined
Oct 9, 2020
Messages
3
Office Version
  1. 365
Platform
  1. Windows
I have two datasets I am comparing for discrepancies, call it table A and table B

Essentially, I want to be able to overlay table B ontop of the first data set to see where the errors, differences and gaps are.

A vlookup for every column is maybe not the best solution, since column names of table A and B are sometimes different, and the sheer size of the tables I want to compare probably mean that an index/match function would be more suitable, however I am not the best at creating these. Would someone be able to help?

I have attached examples of this, essentially what I need is a column that looks up each individual row, but takes into account that not all rows are ordered correctly, so I need a lookup to see what row name matches, and then a look up to see if the information in the columns matches.

Would be much appreciated.

Thanks
 

Attachments

  • example excel.PNG
    example excel.PNG
    31.4 KB · Views: 8

Some videos you may like

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

mrshl9898

Well-known Member
Joined
Feb 6, 2012
Messages
1,484
I'd make a third table below.

=B4=B11 in B16

And copy that across and down
 

Ldoug

New Member
Joined
Oct 9, 2020
Messages
3
Office Version
  1. 365
Platform
  1. Windows
Wouldn’t be that simple because column names (identifiers) are different.

That’s why I think a row look up and then a match would probably be what I’m after.

Datasheet I am working on looks nothing like this so a true/false 3rd table wouldn’t be very helpful

thanks
 

mrshl9898

Well-known Member
Joined
Feb 6, 2012
Messages
1,484
So column names and row identifiers are different, MATCH won't assist. Hard to help when you give an example that "looks nothing like" what you supply.
 

Watch MrExcel Video

Forum statistics

Threads
1,112,860
Messages
5,542,929
Members
410,577
Latest member
ZvK
Top