Excel function to lookup sub-strings in a string

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


Goethestraße 11-13

------ 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.

Example: FIND("Washington";A1;5)

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])

Kemahiran: Visual Basic untuk Aplikasi

Lihat lagi: string find c, string b, number function, function number, a b, Vlookup, strings, str, semicolon, LOOkup, excel vlookup, excel function, excel formula, string find, settings visual basic language, excel additional, english worksheet, excel check, applications excel, lookup excel, number lookup, vlookup excel, additional function, excel english german, use lookup function mysql

Tentang Majikan:
( 34 ulasan ) Munich, Germany

ID Projek: #4083869

Dianugerahkan kepada:


Hi Chgeiselmann It's something I can do. I can write script using vba for macros or write user defined functions with vba. Regards Pascal Baro

$30 USD dalam sehari
(5 Ulasan)

5 pekerja bebas membida secara purata $78 untuk pekerjaan ini


Hi. I interested in your project.

$30 USD dalam 3 hari
(1 Ulasan)

I am really interested in your project, let me do it for you. thanks

$250 USD dalam 5 hari
(0 Ulasan)

I am really interested in your project. Please see PM for details.

$50 USD dalam 3 hari
(0 Ulasan)

i really do this projects i hope you may give me a positive rpelay.

$45 USD dalam 3 hari
(0 Ulasan)

Hi There, This function is easier said that done. I have done an experiment to see how long it will take to create such a function. I don't think that it should take longer than 3 hours for all your requirements. Lagi

$30 USD dalam sehari
(0 Ulasan)