Narration from Bank in front of party name

[Sarfraz]

Dear Sir,

I have got two Sheets one is Ledger Name and another one is BS.
BS has got narration from Bank.
Ledger Name has got Name of party from Books of accounts.
I want get result like Narration from BS in front of Party.
Eg : Party Name is UNIQUE SERVICE I want to get narration from Bank Contains Unique Service as example says.
I have highlighted yellow where the same report is expected.
these are the few example file is big please suggest a good formula.

 

[Marc L]

Hi,

example in cell B4 :

=IF(ISNUMBER(MATCH("*"&A4,BS!$C$2:$C$4,0)),OFFSET(BS!$C$1,MATCH("*"&A4,BS!$C$2:$C$4,0),0))

Ok for B5 but not for B3 'cause of double space in UNIQUE SERVICE in BS!C2.
​

 

[Sarfraz]

Hi,

example in cell B4 :

=IF(ISNUMBER(MATCH("*"&A4,BS!$C$2:$C$4,0)),OFFSET(BS!$C$1,MATCH("*"&A4,BS!$C$2:$C$4,0),0))

Ok for B5 but not for B3 'cause of double space in UNIQUE SERVICE in BS!C2.
​

Thats good sir,

But is there any solution for such a problem like double space, or can you make it basis on first 5 character and last 5 character.

 

[Khalid NGO]

Thats good sir,

But is there any solution for such a problem like double space, or can you make it basis on first 5 character and last 5 character.

Hi Sarfraz,

You can add TRIM in Marc's formula:
=IF(ISNUMBER(MATCH("*"&A3,TRIM(BS!$C$2:$C$4),0)),OFFSET(BS!$C$1,MATCH("*"&A3,TRIM(BS!$C$2:$C$4),0),0))

but now you have to enter with Ctrl+Shift+Enter

and of course you need IF statement to check if the cell is not blank:
=IF(A3="","",IF(ISNUMBER(MATCH("*"&A3,TRIM(BS!$C$2:$C$4),0)),OFFSET(BS!$C$1,MATCH("*"&A3,TRIM(BS!$C$2:$C$4),0),0)))
with CSE

Regards,

 

[Khalid NGO]

Or may be this:
=INDEX(BS!$C$2:$C$4,MATCH(TRIM(RIGHT(A3,LEN(A3))),RIGHT(TRIM(BS!$C$2:$C$5),LEN(A3)),0),)

again with CSE.

 

[Sarfraz]

Hi Sarfraz,

You can add TRIM in Marc's formula:
=IF(ISNUMBER(MATCH("*"&A3,TRIM(BS!$C$2:$C$4),0)),OFFSET(BS!$C$1,MATCH("*"&A3,TRIM(BS!$C$2:$C$4),0),0))

but now you have to enter with Ctrl+Shift+Enter

and of course you need IF statement to check if the cell is not blank:
=IF(A3="","",IF(ISNUMBER(MATCH("*"&A3,TRIM(BS!$C$2:$C$4),0)),OFFSET(BS!$C$1,MATCH("*"&A3,TRIM(BS!$C$2:$C$4),0),0)))
with CSE

Regards,

Works Perfectly.thanks sir.

 

[Khalid NGO]

Works Perfectly.thanks sir.

Glad it worked perfectly
Thanks for the feedback.

Keep visiting.

 

[dklakhani]

whether reverse is possible
i have got two Sheets one is Ledger Name and another one is BS.
BS has got narration from Bank.
Ledger Name has got Name of party from Books of accounts.
I want get result like ledger from books of accounts in front of Narration from Bank

 

[vletm]

dklakhani​

You should open a new own thread for Your own case.
This ... few year old ... thread is closed now.