Bank Reconciliation and Bank Ledger entry finding.

[Shabbo]

Dear Sir,
I have bank statement and bank reconciliation, bank ledger has Vch No. which is available in bank statement either in reference number column or Description column.
I wanted to check entry and amount available in bank ledger.
Data is too large and it will take so long to reconcile can you please help me with a formula.

 

[Chihiro]

Use the formula given in your other thread to extract reference number.

Then use Index/Match or other lookup formula.

 

[Shabbo]

Use the formula given in your other thread to extract reference number.

Then use Index/Match or other lookup formula.

Sir,
Vlookup will not work because in some cases reference number is coming in ref colmn. like search from both column or any other formula.

 

[bosco_yip]

Try...............

In "Bank Ledger" sheet "Bank statement" column J2, formula copied down :

=IFERROR(1/(1/LOOKUP(9.9E+307,SEARCH(G2,'BANK STATEMENT'!$C$13:$C$100),'BANK STATEMENT'!$H$13:$H$100)),"")

Regards
Bosco

 

[Shabbo]

Dear Sir,
Its not working because wherever there are blank showing 60000
Also its not giving a right result in sheet of BANK STATEMENT as well.

Please look into the same.