Compare two tables with two criteria

[George1]

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.

 

[mehidy1437]

See, if it's okay or not?

List all matching values between two named ranges based on criteria.xlsx
	A	B	C	D	E	F	G	H
1	COMPANY-TABLE01			COMPANY-TABLE02		MISSING COMPANY IN-TABLE02	MISSING COMPANY IN-TABLE01	COMPANY EXIST IN BOTH TABLE
2	AA1			AA1		AA4	CC9	AA1
3	AA2			AA2		AA5	CC10	AA2
4	AA3			AA3		BB5	CC11	AA3
5	AA4			BB1		BB6	CC12	BB1
6	AA5			BB2		BB7	CC13	BB2
7	BB1			BB3		BB8	CC14	BB3
8	BB2			BB4		BB9	CC15	BB4
9	BB3			CC1		BB10	CC16	CC1
10	BB4			CC2				CC2
11	BB5			CC3				CC3
12	BB6			CC4				CC4
13	BB7			CC5				CC5
14	BB8			CC6				CC6
15	BB9			CC7				CC7
16	BB10			CC8				CC8
17	CC1			CC9
18	CC2			CC10
19	CC3			CC11
20	CC4			CC12
21	CC5			CC13
22	CC6			CC14
23	CC7			CC15
24	CC8			CC16
Sheet2

Cell Formulas
Range	Formula
G2:G16	G2	=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:F19	F2	=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:A6	A2	="AA"&ROW(A1)
A7:A16	A7	="BB"&ROW(A1)
A17:A24	A17	="CC"&ROW(A1)
D5:D8	D5	="BB"&ROW(D1)
D9:D24	D9	="CC"&ROW(D1)
H2:H24	H2	=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))),"")