Find word in non-uniform string that matches word in different string

[LizT]

Hi there,
I am trying to return the vendor name based on a string of text accounting enters. They do not enter the text in the same format and they may use abbreviations as well. Can anyone think of a formula. Below is an example:

Vendor List in SAP:
A VENDOR
ANOTHER ONE
CHANDOO
ONE MORE
RIGHT VENDOR
SOMETHING ELSE
VERY GOOD WORK


Accounting Manual Entry:
example 1) Mar 2016 VGW invoice number 102654
example 2) Reclass SOMETHING ELSE from one account to another
example 3) 1234597 Jan16 VERY GOOD WORK for marketing
example 4) accounting doesn't use the same format VGW for anything
example 5) One More accrual 1/2016


Returned value:
answer 1) VERY GOOD WORK
answer 2) SOMETHING ELSE
answer 3) VERY GOOD WORK
answer 4) VERY GOOD WORK
answer 5) ONE MORE



any help is greatly appreciated!
Thank you in advance.

 

[bosco_yip]

Try,

1] Set up a Searching List, with Criteria and Returned names (e.g. D2:E9)

2] Data in Column A

3] In B1, formula copy down :

=LOOKUP(2,1/SEARCH(D$2:D$9,A1),E$2:E$9)

4] See attached file

Regards
Bosco

 

[LizT]

Thank you, this is a good solution. It may be a bit cumbersome to maintain the criteria name as there are 1000s of vendors and many different interpretations to abbreviate in addition to human error and misspelled words. I will definitely give this a try.

 

[Shteven]

LizT, the reality of a freeform text field is that users will find an infinite number of different ways to enter the same information, so it will never be easy.

Perhaps the solution would be to come up with a company policy to enter certain codes for each vendor to be used when entering invoices. It won't eliminate human error 100% but will be much more manageable than your current situation.

 

[azumi]

Not sure but give it try................UDF with array formula

 

[Dalia]

can you please make me understand how the formula LOOKUP(2,1/SEARCH(D$2:D$9,A1),E$2:E$9) is working. why that 1/SEARCH???

 

[Atlas Johnson]

I have created a file where you can get the results. I hope it works for you.

Basicly what it does is;

• Search all the vendors each Accounting Entry
• Find the last one that matches if it has more than one match
• Lookup the SAP code for it

Since it uses Array formulas, click Ctrl+Shift+Enter or Ctrl+Alt+F9 to calculate.
Cheers,
AJ