wild cards in Vlookup

grtbluyonder · Newbie Joined: 23 Apr 2009 Posts: 1 Location: Canada

I cannot get wild cards to work in Vlookup.

Vlookup(A3;A4:E1000,2,0) where A3 needs to be able to use wildcards.

For example A3 could contain:
Frankfurt

A4:E1000 could contain
Frankfurt
Frankfurt Muni
Frankfurt Municipal

I want Vlookup to find Frankfurt* in other words find it whether it is any of the three formats. The lookup list will only contain one of the three forms (it could be any of them) but I want the first instance with frankfurt returned.

I've tried Vlookup(A3*;A4:A1000,2,0) to no avail.

What is the format for wildcards where the wild characters are AFTER the initial search text?

Villeroy · Posted: Fri Apr 24, 2009 2:44 am Post subject:

OOo can not handle the usual wild cards. It uses regular expressions. Put a dot in front of the asterisc and make shure that regular expressions are enabled in Tools>Options...Calc>Calculation...
There is a tweak for Excel compatibility: http://www.oooforum.org/forum/viewtopic.phtml?t=77194

WIthout using any pattern matching at all you can turn off option "Match whole cells" for the document in question.
Case sensitivity is the third relevant option when looking up text values. Numbers are less ambiguous.
_________________
Rest in peace, oooforum.org
Get help on http://forum.openoffice.org