tilda in VLOOKUP and MATCH

[Jerry W. Lewis]

What exactly do VLOOKUP and MATCH do with tildas (~) in text strings?

=VLOOKUP("Hello~ World",array,2,0)
will match "Hello World" but not "Hello~ World"

=MATCH("Hello~ World",array,0)
will not match either string.

I am using Excel 2003, and Help does not say anything about this for either
function.

Jerry

 

[Sean Timmons]

If you want to find Hello~ World, do:

VLOOKUP("Hello~~ World",array,2,0)

Tilde is a wildcard search. double tilde negates the wildcard concept.

 

[Dave Peterson]

Just to add...

Excel can use wildcards:
* for any characters
? for any single character
~ as an "escape" character to indicate that the next * or ? should be treated as
an asterisk or question mark--not as wild cards.

So if you're using an =vlookup() for instance, you can avoid wildcard problems
with a formula like:

=VLOOKUP(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"~","~~"),"?","~?"),"*","~*"),
Sheet2!$A:$B,2,FALSE)