SOLVED
Home

Match a Bank Statement to Loan (By amount and name)

%3CLINGO-SUB%20id%3D%22lingo-sub-794425%22%20slang%3D%22en-US%22%3EMatch%20a%20Bank%20Statement%20to%20Loan%20(By%20amount%20and%20name)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-794425%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20a%20portfolio%20of%20loans%2C%20which%20have%20different%20amounts%20and%20names.%20However%20there%20is%20a%20chance%20amounts%20or%20names%20will%20over%20lap.%20Can%20i%20extend%20a%20match%20index%20or%20vlookup%20formula%20to%20match%20the%20loan%20name%20and%20amount%20to%20contents%20of%20a%20bank%20statement%2C%20and%20return%20the%20date%20it%20was%20paid.%20If%20possible%20to%20search%20between%20certain%20dates.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20attached%20an%20example.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECan%20someone%20please%20assist.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EKind%20regards%2C%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-794425%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20on%20mobile%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EPower%20BI%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-794503%22%20slang%3D%22en-US%22%3ERe%3A%20Match%20a%20Bank%20Statement%20to%20Loan%20(By%20amount%20and%20name)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-794503%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F364226%22%20target%3D%22_blank%22%3E%40calof1%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20fill%20in%20your%20sample%20file%20with%20sample%20data%20and%20manually%20entered%20sample%20results.%20Thereafter%2C%20attach%20your%20sample%20file%20again.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-794739%22%20slang%3D%22en-US%22%3ERe%3A%20Match%20a%20Bank%20Statement%20to%20Loan%20(By%20amount%20and%20name)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-794739%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F364226%22%20target%3D%22_blank%22%3E%40calof1%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAttached%20are%203%20different%20files.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFirst%20file%20(%3CSTRONG%3Eif%20loan%20amount%20is%20not%20over%20lap%3C%2FSTRONG%3E)%20will%20search%20Payment%20date%20on%20'%3CSTRONG%3ELoan%20Amount%3C%2FSTRONG%3E'.%20I%20have%20put%20round%20function%20(up%20to%202%20decimals)%20in%20column%20J%20since%20it%20appears%20like%20this%20in%20the%20bank%20statement.%20Below%20formula%20is%20used%20in%20column%20K%3A%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3CFONT%20color%3D%22%230000FF%22%3E%3DIFERROR(INDEX(%24A%244%3A%24A%2450%2CMATCH(J6%2C%24D%244%3A%24D%2450%2C0))%2C%22Unpaid%22)%3C%2FFONT%3E%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESecond%20file%20(%3CSTRONG%3Eif%20loan%20name%20is%20not%20over%20lap)%26nbsp%3B%3C%2FSTRONG%3Ewill%20search%20Payment%20date%20on%20'%3CSTRONG%3ELoan%20Name%3C%2FSTRONG%3E'.%20Here%20you%20need%20to%20ensure%20that%20bank%20always%20uses%20the%20same%20method%20to%20combine%20text%20in%20'Transaction%20Description%22%20column.%26nbsp%3B%20%26nbsp%3BIn%20case%20different%20banks%20use%26nbsp%3B%20different%20method%20then%20this%20logic%20will%20have%20to%20be%20changed.%20Below%20formula%20is%20used%20in%20column%20K%3A%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3CFONT%20color%3D%22%23339966%22%3E%3DIFERROR(INDEX(%24A%244%3A%24A%2450%2CMATCH(F6%2C%24E%244%3A%24E%2450%2C0))%2C%22Unpaid%22)%3C%2FFONT%3E%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%3CBR%20%2F%3EThird%20file%20(%3CSTRONG%3Eif%20loan%20amount%20or%20name%20is%20over%20lap)%26nbsp%3B%3C%2FSTRONG%3Ewill%20work%20on%20both%20(Name%20%26amp%3B%20Amount).%20Here%20again%20Loan%20amount%20in%20column%20J%20needs%20to%20be%20rounded%20off.%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3CFONT%20color%3D%22%23993366%22%3E%3DIFERROR(INDEX(%24A%244%3A%24A%2450%2CMATCH((F6%26amp%3B%22%7C%22%26amp%3BJ6)%2C%24E%244%3A%24E%2450%2C0))%2C%22Unpaid%22)%3C%2FFONT%3E%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHope%20it%20will%20help.%3C%2FP%3E%3CP%3EPlease%20let%20me%20know%20if%20it%20works%20for%20you.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%3C%2FP%3E%3CP%3ETauqeer%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-796505%22%20slang%3D%22en-US%22%3ERe%3A%20Match%20a%20Bank%20Statement%20to%20Loan%20(By%20amount%20and%20name)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-796505%22%20slang%3D%22en-US%22%3E%3CP%3EHi%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F239215%22%20target%3D%22_blank%22%3E%40tauqeeracma%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20for%20your%20help%20with%20this.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20just%20added%20a%20criteria%20to%20include%20between%20dates.%20So%20that%20if%20the%20bank%20statement%20covers%20long%20period%2C%20it%20only%20matches%20for%20my%20chosen%20date%20range.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20attached%20an%20example.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECan%20you%20please%20assist.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EKind%20regards%2C%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-797060%22%20slang%3D%22en-US%22%3ERe%3A%20Match%20a%20Bank%20Statement%20to%20Loan%20(By%20amount%20and%20name)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-797060%22%20slang%3D%22en-US%22%3E%3CP%3EHI%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F364226%22%20target%3D%22_blank%22%3E%40calof1%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20modified%20the%20formula%20in%20column%20E%2C%20now%20it%20will%20only%20pick%20those%20loans%20that%20fall%20between%20the%20date%20range%20defined%20in%20%3CSTRONG%3EH1%3C%2FSTRONG%3Eand%20%3CSTRONG%3EJ1.%26nbsp%3B%3C%2FSTRONG%3EIn%20addition%20relevant%20'Payment%20Date'%20will%20also%20be%20highlighted%20for%20better%20view.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHope%20it%20matches%20your%20requirements.%3C%2FP%3E%3CP%3ETauqeer%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-799336%22%20slang%3D%22en-US%22%3ERe%3A%20Match%20a%20Bank%20Statement%20to%20Loan%20(By%20amount%20and%20name)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-799336%22%20slang%3D%22en-US%22%3E%3CP%3EThanks%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F239215%22%20target%3D%22_blank%22%3E%40tauqeeracma%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20matches%20my%20needs%20perfectly.%20Thanks%20for%20your%20help%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-799399%22%20slang%3D%22en-US%22%3ERe%3A%20Match%20a%20Bank%20Statement%20to%20Loan%20(By%20amount%20and%20name)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-799399%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F364226%22%20target%3D%22_blank%22%3E%40calof1%3C%2FA%3E%26nbsp%3B%20You%20are%20welcome.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Frequent Contributor

Hi,

 

I have a portfolio of loans, which have different amounts and names. However there is a chance amounts or names will over lap. Can i extend a match index or vlookup formula to match the loan name and amount to contents of a bank statement, and return the date it was paid. If possible to search between certain dates.

 

I have attached an example.

 

Can someone please assist.

 

Kind regards,

6 Replies

@calof1 

Please fill in your sample file with sample data and manually entered sample results. Thereafter, attach your sample file again. 

Solution

Hi @calof1 

 

Attached are 3 different files.

 

First file (if loan amount is not over lap) will search Payment date on 'Loan Amount'. I have put round function (up to 2 decimals) in column J since it appears like this in the bank statement. Below formula is used in column K:

=IFERROR(INDEX($A$4:$A$50,MATCH(J6,$D$4:$D$50,0)),"Unpaid")

 

Second file (if loan name is not over lap) will search Payment date on 'Loan Name'. Here you need to ensure that bank always uses the same method to combine text in 'Transaction Description" column.   In case different banks use  different method then this logic will have to be changed. Below formula is used in column K:

=IFERROR(INDEX($A$4:$A$50,MATCH(F6,$E$4:$E$50,0)),"Unpaid")


Third file (if loan amount or name is over lap) will work on both (Name & Amount). Here again Loan amount in column J needs to be rounded off.

=IFERROR(INDEX($A$4:$A$50,MATCH((F6&"|"&J6),$E$4:$E$50,0)),"Unpaid")

 

Hope it will help.

Please let me know if it works for you.

 

Thanks

Tauqeer

Hi@tauqeeracma 

 

Thanks for your help with this.

 

I have just added a criteria to include between dates. So that if the bank statement covers long period, it only matches for my chosen date range.

 

I have attached an example.

 

Can you please assist.

 

Kind regards,

HI@calof1 

 

I have modified the formula in column E, now it will only pick those loans that fall between the date range defined in H1 and J1. In addition relevant 'Payment Date' will also be highlighted for better view.

 

Hope it matches your requirements.

Tauqeer

 

 

Thanks@tauqeeracma 

 

This matches my needs perfectly. Thanks for your help

@calof1  You are welcome.

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies