Compare two tables with two criteria

George1

New Member
Joined
Jun 26, 2009
Messages
12
Hi there, I am trying to match two tables of data to bring back a result. Table A is the master table. I want to identify what transactions are missing in Table B when compared to A. I was trying to do an XMatch to compare both tables based on the criteria of amount. As you will see from my example, Table A is laid out slightly differently to Table B. Table A is showing the figures in two columns side by side and Table B is showing them in one list. I want to know what is the best approach or formula to compare both tables on one or two criteria i.e. amount and/or company name to bring back a match or no match result? Regards George.
 

Attachments

  • matching exercise.PNG
    matching exercise.PNG
    66.9 KB · Views: 3

Some videos you may like

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

mehidy1437

Active Member
Joined
Nov 15, 2019
Messages
252
Office Version
  1. 2013
Platform
  1. Windows
  2. Mobile
  3. Web
See, if it's okay or not?
List all matching values between two named ranges based on criteria.xlsx
ABCDEFGH
1COMPANY-TABLE01COMPANY-TABLE02MISSING COMPANY IN-TABLE02MISSING COMPANY IN-TABLE01COMPANY EXIST IN BOTH TABLE
2AA1AA1AA4CC9AA1
3AA2AA2AA5CC10AA2
4AA3AA3BB5CC11AA3
5AA4BB1BB6CC12BB1
6AA5BB2BB7CC13BB2
7BB1BB3BB8CC14BB3
8BB2BB4BB9CC15BB4
9BB3CC1BB10CC16CC1
10BB4CC2  CC2
11BB5CC3  CC3
12BB6CC4  CC4
13BB7CC5  CC5
14BB8CC6  CC6
15BB9CC7  CC7
16BB10CC8  CC8
17CC1CC9  
18CC2CC10  
19CC3CC11  
20CC4CC12 
21CC5CC13 
22CC6CC14 
23CC7CC15 
24CC8CC16 
Sheet2
Cell Formulas
RangeFormula
G2:G16G2=IFERROR(INDEX($D$2:$D$24,AGGREGATE(15,6,(ROW($D$2:$D$24)-ROW($D$2)+1)/(ISNA(MATCH($D$2:$D$24,$A$2:$A$24,0))),ROWS(G$1:G1))),"")
F2:F19F2=IFERROR(INDEX($A$2:$A$24,AGGREGATE(15,6,(ROW($A$2:$A$24)-ROW($A$2)+1)/(ISNA(MATCH($A$2:$A$24,$D$2:$D$24,0))),ROWS(F$1:F1))),"")
D2:D4,A2:A6A2="AA"&ROW(A1)
A7:A16A7="BB"&ROW(A1)
A17:A24A17="CC"&ROW(A1)
D5:D8D5="BB"&ROW(D1)
D9:D24D9="CC"&ROW(D1)
H2:H24H2=IFERROR(INDEX($A$2:$A$24,AGGREGATE(15,6,(ROW($A$2:$A$24)-ROW($A$2)+1)/(ISNUMBER(MATCH($A$2:$A$24,$D$2:$D$24,0))),ROWS(H$1:H1))),"")
 

Watch MrExcel Video

Forum statistics

Threads
1,113,745
Messages
5,543,959
Members
410,586
Latest member
acadavid86
Top