Aug 07 2019 09:39 PM
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,
Aug 07 2019 11:38 PM
Please fill in your sample file with sample data and manually entered sample results. Thereafter, attach your sample file again.
Aug 08 2019 01:58 AM
SolutionHi @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
Aug 08 2019 10:20 PM
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,
Aug 09 2019 02:17 AM
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
Aug 11 2019 05:13 PM
Aug 11 2019 09:29 PM
@calof1 You are welcome.
Aug 08 2019 01:58 AM
SolutionHi @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