Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
tilda in VLOOKUP and MATCH
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
tilda in VLOOKUP and MATCH
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. "Jerry W. Lewis" wrote: 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
tilda in VLOOKUP and MATCH
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) Jerry W. Lewis wrote: 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 -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
second or third match in vlookup() or Match() | Excel Worksheet Functions | |||
How do you put a tilda above a letter ? | Excel Worksheet Functions | |||
Vlookup when no value match | Excel Worksheet Functions | |||
index Match, or Vlookup Match.. | Excel Worksheet Functions | |||
Match with tilda characater | Excel Worksheet Functions |