SOLVED

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

Highlighted
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
Highlighted

# 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.

Highlighted
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

Highlighted

# 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,

Highlighted

# 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

Highlighted

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

Thanks@tauqeeracma

This matches my needs perfectly. Thanks for your help

Highlighted

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

@calof1  You are welcome.