Question: Vlookup and tilda ("~")

Aladin Akyurek

MrExcel MVP

Joined

Feb 14, 2002

Messages

85,210

• Apr 23, 2003

• #2

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)

 

Upvote 0

M

Mark W.

MrExcel MVP

Joined

Feb 10, 2002

Messages

11,654

• Apr 23, 2003

• #3

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

 

Upvote 0

Barry Katcher

Well-known Member

Joined

Feb 25, 2002

Messages

4,053

• Apr 23, 2003

• #4

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.

 

Upvote 0

M

Mark W.

MrExcel MVP

Joined

Feb 10, 2002

Messages

11,654

• Apr 23, 2003

• #5

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.

Click to expand...

 

Upvote 0