• 463K Members
• 10.4K Online
• 560K Conversations
SOLVED

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

Frequent Contributor

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

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.

Kind regards,

6 Replies

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

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

Solution

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

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

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

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.

Kind regards,

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

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.

Tauqeer

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

Thanks@tauqeeracma

This matches my needs perfectly. Thanks for your help

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

@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