# Compare two tables with two criteria

#### George1

##### New Member
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
66.9 KB · Views: 3

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

Replies
2
Views
48
Replies
1
Views
84
Replies
14
Views
96
Replies
3
Views
103
Replies
1
Views
38