"Ada33" wrote:
ID Amount ID Amount
1234 $100 4213 $80
2134 $80 1234 $100
3124 $120 2134 $80
4213 $100 4412 $60

If it helps, I am doing a reconciliation. Essentially, if
the ID exists in both lists and is the same, then it's fine
and nothing needs to happen (in the above example, that
would be for ID 1234 and 2134). However, if an ID is only
on one list or if the amounts are not equal, I
want it to be flagged.

Suppose your data are in columns A and B on the left and columns D and E on
the right, with the titles in row 1.

Enter the following formulas into the indicated cells and copy down as
needed.

C2: =IF(A2="","",
IF(AND(COUNTIF($A$2:$A$1000,A2)=1,COUNTIF($D$2:$D$ 1000,A2)=1),
IF(VLOOKUP(A2,$D$2:$E$1000,2,0)=B2,"","ERROR"),"ER ROR"))

F2: =IF(D2="","",
IF(AND(COUNTIF($A$2:$A$1000,D2)=1,COUNTIF($D$2:$D$ 1000,D2)=1),
IF(VLOOKUP(D2,$A$2:$B$1000,2,0)=E2,"","ERROR"),"ER ROR"))

This assumes each ID should appear only once in each column, and the amounts
should be the same.

If you want to allow for multiple appearances of the same ID and only the
total of each sum should be the same (partial invoices and partial payments,
not necessarily a one-to-one match), try the following:

C2: =IF(A2="","",
IF(AND(COUNTIF($A$2:$A$1000,A2)=1,COUNTIF($D$2:$D $1000,A2)=1),
IF(SUMIF($A$2:$A$1000,A2,$B$2:$B$1000)=SUMIF($D$2: $D$1000,A2,$E$2:$E$1000),
"","ERROR"),"ERROR"))

F2: =IF(A2="","",
IF(AND(COUNTIF($A$2:$A$1000,D2)=1,COUNTIF($D$2:$D $1000,D2)=1),
IF(SUMIF($A$2:$A$1000,D2,$B$2:$B$1000)=SUMIF($D$2: $D$1000,D2,$E$2:$E$1000),
"","ERROR"),"ERROR"))