return value if cell contains specific text

little jimmy

New Member
Joined
Mar 21, 2013
Messages
13
Hi,

I am trying to automate a way of searching through my bank statement and returning a reference number based on the narrative that appears on the statement (sample data attached). We get hundreds of items every day and the narrative that is quoted on the bank statement is always different (often due to different invoice number being quoted), BUT there will always be certain "key" text that is always quoted.

eg BMW will always be quoted, but the statement line may state "BMW motors" or "BMW july" payment or "Invoice for BMW"

I have a list of the "key" text and the relevant reference number that is needed for our Ledger system.

I need a formula that will look at each bank statement line, search the list of key text and return the relevant ref number.


If I were to write out the formula it would read something like "look at the first line on the bank statement (cell A2), if the narrative quoted contains a key word from column A of the the named range, then return the customer ref from column B of the named range into cell C2, if the narrative does not contain a key word then leave C2 blank. Repeat this for every statement line.

I have created a simplified mock up in excel but not sure how to attach a screen shot to this thread.

I have tried using =if(isnumber(search(.... but cannot figure it out.

Many thanks.
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
Hey,

How about COUNTIF using wildcards?

E.g. =COUNTIF($A$1:$A$10,"*BMW*") will return how many cells in the range A1:A10 that CONTAIN "BMW"
 
Last edited:
Upvote 0
OK,

Is the reference number always numeric? I.e. =ISNUMBER(references) always returns TRUE?
 
Upvote 0
try
Code:
=IF(ISNUMBER(SEARCH("bmw",A2)),B2,"")

how many narratives are there?
and do you want to search for the narratives at the same time?
 
Last edited:
Upvote 0
We get about 150 statement lines every day, each is a payment from a customer. The list of key words is 2000+, one for each of our 2000+customers.

I need to see if any of the 2000+ key words appears in the statement narrative, if it does then I need to return a customer reference associated with that key word.

So for line 1 on the statement I need to look at the 2000+key words and see if any of them appear in the narrative (and return the relevant cust ref if it does)
Then for line 2 on the statement I need to look at the 2000+key words and see if any of them appear in the narrative (and return the relevant cust ref if it does)
then for line 3 on the statement I need to look at the 2000+key words and see if any of them appear in the narrative (and return the relevant cust ref if it does)
and so on...

If there is a way to add screen shots or an attachment that might help as they say a picture is worth a thousand words.
 
Last edited:
Upvote 0
If there is a way to add screen shots or an attachment that might help as they say a picture is worth a thousand words.

yes i think i get the idea but you can use HTML Maker and other tools to attach what you're talking about.
but as i understand you need an array formula that says if ANY of these 2000+ keywords are found, return the value for column B (the customer ref number)? I would suggest these things:

1. make the keywords a named range on another sheet
2. post your screenshot of what you currently have
3. post your screenshot of what you expect it to look like

& then i'll make a formula that meets what you need, but i don't want to start and it end up being not what you're explicitly asking for.
 
Upvote 0
I believe it can be done with SUMPRODUCT if the cust ref number is numerical only, otherwise a different approach is required.

Are the reference numbers numerical or alphanumerical?

[TABLE="class: grid, width: 750"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Invoice for Vauxhall[/TD]
[TD]123[/TD]
[TD][/TD]
[TD]BMW[/TD]
[TD]1738[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Audi Repair[/TD]
[TD]9215[/TD]
[TD][/TD]
[TD]Audi[/TD]
[TD]9215[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]BMW Repairs[/TD]
[TD]1738[/TD]
[TD][/TD]
[TD]Jaguar[/TD]
[TD]1983[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Jaguar Repairs[/TD]
[TD]1983[/TD]
[TD][/TD]
[TD]Vauxhall[/TD]
[TD]123[/TD]
[/TR]
</tbody>[/TABLE]


If numerical you can get this using formula in B1 and dragged down:
Code:
SUMPRODUCT((ISNUMBER((SEARCH($D$1:$D$4,A1))))*($E$1:$E$4))
 
Last edited:
Upvote 0
Apologies for the length of the reply before we begin.

I have created some sample data below. Each table is on a different tab in the same workbook.
The first table shows the list of key words and the corresponding customer reference. I have named the range A1:B9 as "key".

[TABLE="width: 200"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Key word[/TD]
[TD]Cust ref[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Alfa[/TD]
[TD]CUS-001[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Aston[/TD]
[TD]CUS-002[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]BMW[/TD]
[TD]CUS-003[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Ford[/TD]
[TD]CUS-004[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Honda[/TD]
[TD]CUS-005[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Mercedes[/TD]
[TD]CUS-006[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]Nissan[/TD]
[TD]CUS-007[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]Vauxhall[/TD]
[TD]CUS-008[/TD]
[/TR]
</tbody>[/TABLE]

The second table shows the narrative that appears on the bank statement (column A).

The formula needs to go in column B and I have put the answer I would expect to receive in this column. Any blanks are where there is no match to a key word (ie there is no "A Romero", no "Merc" but there is "Mercedes", no "vaux" but there is "vauxhall", no "Nisan")



[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Statement narrative[/TD]
[TD]Customer ref[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]BMW motors uk ltd[/TD]
[TD]CUS-003[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Ford motor company[/TD]
[TD]CUS-004[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]general motors (vauxhall)[/TD]
[TD]CUS-008[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Nissan (Feb invoice)[/TD]
[TD]CUS-007[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Aston Martin Motor co[/TD]
[TD]CUS-002[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]A Romeo[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]Merc Benz[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]Ford (inv ref 5927)[/TD]
[TD]CUS-004[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]Nissan Motors[/TD]
[TD]CUS-007[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]GM Motors (vaux)[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]BMW uk[/TD]
[TD]CUS-003[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]Nissan motors[/TD]
[TD]CUS-007[/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]Mercedes Benz ref 2330d[/TD]
[TD]CUS-006[/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]Ford (USA) Inc[/TD]
[TD]CUS-004[/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD]Vauxhall vehicle sales[/TD]
[TD]CUS-008[/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD]Honda - Japan[/TD]
[TD]CUS-005[/TD]
[/TR]
[TR]
[TD]18[/TD]
[TD]Ford (inv ref 3009)[/TD]
[TD]CUS-004[/TD]
[/TR]
[TR]
[TD]19[/TD]
[TD]Nisan motors[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]20[/TD]
[TD]BMW (July 2019)[/TD]
[TD]CUS-003[/TD]
[/TR]
</tbody>[/TABLE]


The formula would read something like:
lookup the statement narrative in cell A1 (BMW motors uk Ltd), if any of the key words in the named range "key" appear in cell A1 (BMW does appear), then return the cust ref for that key word (in this case CUS-003).

the next line would read
lookup the statement narrative in cell A2 (Ford motor company), if any of the key words in the named range "key" appear in cell A2 (Ford does appear), then return the cust ref for that key word (in this case CUS-004).
 
Upvote 0

Forum statistics

Threads
1,223,099
Messages
6,170,107
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top