I am trying to re-create what Lookup is doing in Excel.
The data is an extract of journal entries from our accounting system. One of the fields, Description, contains multiple data elements, one of which is the company name. (To make matters worse, the field is free-form and our sales team doesn't enter the data in a consistent format.)
In order to parse the data, the previous developer wrote a LOOKUP that also uses a MATCH with wildcards to scan the Description field for the company name in column A in another tab, and then get the corporate alias that corresponds from column B of the array. (If there is no match, it defaults to OTHER.) This is the Excel formula:
=IFERROR(LOOKUP(10^99,MATCH("*"&'Corp Account Lookup'!$A$2:$A$900&"*",Q439,0)/('Corp Account Lookup'!$A$2:$A$900<>""),'Corp Account Lookup'!$B$2:$B$900),"OTHER")
I've been trying to replicate it for two days but having no luck at all. The closest I came was doing a WildMatch of the known company names to the description - it worked, but it also resulted in multiple hits because the patterns exists multiple times.
Anyone have any suggestions?
