Vlookup Bug "~"

[Karthik]

I found that the vlookup function fails to look for a value which has
got the character"~" in it. I have work around(s) to solve this issue.

All I want to know is "Why does Vlookup function fail in this case?"

Example : Copy paste the below example and try it yourself.
The formula in cell B2 =VLOOKUP(B3,$D$3:$E$14,2,0) gives an error.


A B C D E
1 Input Output Vlookup Array
2 ~ #N/A ~ 1
3 ! 2
4 @ 3
5 # 4
6 $ 5
7 % 6
8 & 7
9 ^ 8
10 * 9
11 ( 10
12 ) 11
13 _ 12



Thanks
Karthik Bhat

 

[Dave Peterson]

Excel supports wild cards.

The asterisk (*) represents any set of characters. The question mark (?)
represents one character.

You can specify that you really want the asterisk or question mark by using ~*
or ~?. Since ~ is used as this "escape" character, you have to use ~~ when you
want to find that ~ character.

One way you can avoid all 3 potential problems:

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

You can remove the ? and * portion if you know your data doesn't have them--but
they won't hurt if you keep them.

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

 

[Karthik]

Thanks Dave

That's a very neat and simple solution. And thanks for the explanation

Karthik Bhat