Question: Vlookup and tilda ("~")

[Sparkwood]

Hi - Just a quick question:

Does anybody know of a way to get the vlookup function to work when the lookup value contains the tilda symbol.

Many thanks,

S.

 

[Aladin Akyurek]

Book1
	C	D	E	F	G
1
2	~a	1		~a	1
3	b	2		b	2
4	c	3		c	3
5
Sheet1

The formula in G2 is...

=VLOOKUP(IF(ISNUMBER(SEARCH("~~",F2)),SUBSTITUTE(F2,"~","~~"),F2),$C$2:$D$4,2,0)

 

[Mark W.]

Book1
	A	B	C	D	E	F	G	H	I	J
1
2	~a	1		~a	1
3	~~b	2		~~b	2
4	c	3		c	3
5
Sheet4

 

[Barry Katcher]

Aladin & Mark, I'm just wondering why a standard VLOOKUP treats a tilde differently. In the formula bar, ~a shows up as text, the same as b or c.

 

[Mark W.]

I don't believe that it's documented. VLOOKUP behaves like MATCH if it's 4th argument is 0 or FALSE.

From the MATCH Help topic Remarks...
If match_type is 0 and lookup_value is text, lookup_value can contain the wildcard characters asterisk (*) and question mark (?). An asterisk matches any sequence of characters; a question mark matches any single character.