excelvbo

I want to match two columns against each other in excel

I want to compare two columns named Debit and Credit to check that every transaction amount in Debit column is present in the Credit column. I have used a MATCH and VLOOKUP but they only match transactions in Debit against the Credit but I need to also match transactions in the Credit against the debit at the same instance.enter image description here

Solution

  • If I understand correctly, put the formule below in C2 and drag it all the way down.

    =IF(ISBLANK(A2),IFERROR("Debit transaction n°" & MATCH(B2,$A$2:$A$21,0)+1, "Debit transaction not found!"),IFERROR("Credit transaction n°" & MATCH(A2,$B$2:$B$21,0)+1, "Credit transaction not found!"))

    enter image description here

    EDIT :

    =IF(ISBLANK(A2),IF((IF(B2<>"",(COUNTIF($B$2:$B2,$B2)=1)+0,0))=1,IFERROR("Debit transaction n°"&MATCH(B2,$A$2:$A$24,0)+1,"Debit transaction not found!"),"Debit transaction not found!"), IF((IF(A2<>"",(COUNTIF($A$2:$A2,$A2)=1)+0,0))=1,IFERROR("Credit transaction n°"&MATCH(A2,$B$2:$B$24,0)+1,"Credit transaction not found!"),"Credit transaction not found!"))

    enter image description here