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 |
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 |
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 |
All times are GMT +1. The time now is 08:32 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com