I need a function for Excel (Version 2007 and higher) that does the following:
Check the string in a given cell in the same worksheet (e.g. cell A1) for instances of strings listed in a separate worksheet (called here: LUStreets) in column A. [This is, so far, what VLOOKUP with “range_lookup=TRUE” would do).]
If the string is found at any (!!!) position in the string in A1 (be it at the beginning*, be it in the middle or the end), the function pastes a string taken from LUStreets column B. [This exceeds what VLOOKUP can do, especially with respect to “at any position”].
---- Example: ----
Neustadt, WBZ, Goethestr. 11/13 , R 105 Computerraum
Goethestr Goethestraße 11-13
Blumenw Blumenweg 53
Washingtonp Washingtonplatz 2
Fritz-Müller-Str Fritz-Müller-Straße 28
Brauereigasse Brauereigasse 15
Brauerei-Gasse Brauereigasse 15
------ End of the example ------
I would like to have a function, not a formula, to more easily use it whenever needed.
The function will have a number of parameters, at least:
lookupstring(Cell where to check; Range with lookup values)
Additional feature (if possible): Lookup case sensitive / non case sensitive [would be a third parameter]
Attention, I am using English Excel, but with German language settings, so semicolon is used in stead of comma to separate parameters.
Please include also the following requirement:
When no match has been found, an empty string ("") will be the output, unless I set a deliberate other string in the function's parameters.
The parameters will be, therefore:
lookupstrings(Where to check; Range with lookup values; [output if no match was found]; [switch on/off case sensitive])