Auto Allocate Account from the Master Sheet

[nitkot]

Hi,
I want to allocate the financial accounts based on the narration string in the Bank statement, I have created one sample excel workbook with two sheets.

There is a "Statement" sheet, which is the snapshot of Bank statement, I want macro or formula to read "Narration" column and pick-up the related accounts from the "Master" sheet and keep in "Account" column of "Statement" sheet.

Can anyone help in this?

Thanks in advance
Nitin Kothari

 

[Sanjeev1976]

You can try the below formula :

=IFERROR(INDEX(Master!$B$3:$B$7,MATCH(1,COUNTIF(A3,"*"&Master!$A$3:$A$7&"*"),0)),"Not there in list")

 

[nitkot]

You can try the below formula :

=IFERROR(INDEX(Master!$B$3:$B$7,MATCH(1,COUNTIF(A3,"*"&Master!$A$3:$A$7&"*"),0)),"Not there in list")

Hi Sanjeev,

Thanks for your response, unfortunately this solution is not working, Check the attached image. I would clarify further that, if any word within Statement-->Narration matches Master-->Keyword, then Master-->Account should be pasted in Statement-->Account.

I am sending the expected result also herewith.

Expected Results

Thanks
Nitin

 

[Sanjeev1976]

Hello Nitin,

Can you send a sample file or sample data and then will update the formula and send. For me it is working exactly the way you want.

 

[nitkot]

Hi Sanjeev,

Thanks for your response, I am new to this blog, Can you let me know, How do I send excel sheet in this thread?

 

[Sanjeev1976]

You can use the XL2BB addin from this platform and upload your data

https://www.mrexcel.com/board/mrexcel-xl2bb.svg

 

[nitkot]

I am sorry to drag you in installation of the xl2bb, when I have downloaded and trying to add into excel as add-in, system is giving me an error message, Any solution?