Vlookup with tilde

  • Thread starter Thread starter Angus
  • Start date Start date
A

Angus

hi,

I am doing a vlookup and the lookup value is "~" (the
tilde). Is the tilde a viable lookup value?

Thanks in advance,

Angus
 
The tilde acts as an escape character in VLOOKUP. Use two tildes
together:

=VLOOKUP("~~",A:B,2,FALSE)
 
Thanks that's an inprovement but the problem is that the
lookup is down a page and the tilde may show up randomly
through the page.

The code I have is ... VLOOKUP(C318,[RateRecWithRepJul.xls]
ReasonCode!$A$6:$D$99,2,FALSE). I will reconise the tilde
if I put the code as VLOOKUP(C318&C318
[RateRecWithRepJul.xls]ReasonCode!$A$6:$D$99,2,FALSE) but
this stops the function working for the rest of the
lookups.

Any help would be appreciated.

Thanks,

Angus
 
And just because...

Excel supports wild cards:
* = any string of characters
? = any one character

and the way you tell excel that you want to use an asterisk is to prefix it with
~ (~*).

And since that ~ is a special character, you have to tell excel to use two when
you want to use 1. ~ becomes ~~.

So this could be a formula that "fixes" all 3 of those special characters:

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