Using VLOOKUP where data may contain "~" characters

  • Thread starter Thread starter David Johnson
  • Start date Start date
D

David Johnson

Hello,

I'm trying to use VLOOKUP to determine which row in a table corresponds to
my data value (or return #N/A if it's not there). All goes fine, except
when the data contains a "~" - which appears to be a special escape
character as far as Excel is concerned (see example below).

As a temporary measure I've replaced "~" with "$" in my data, but I'd like
to know if there is a better solution.

Result = VLOOKUP(A2,$B$2:$C$6,2,FALSE)

Data Lookup Table Result
a@a a!a 1 3
a~a 2
a@a 3
a#a 4
a$a 5


---------------------------------------------------

Data Lookup Table Result
a~a a!a 1 #N/A
a~a 2
a@a 3
a#a 4
a$a 5


Thanks for any help you can give.

Regards,
Dave.
 
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)
(one cell)

And if you didn't care about the * or ?, the formula would be:
=VLOOKUP(SUBSTITUTE(A1,"~","~~"),Sheet2!$a:$b,2,FALSE)
 
Thank you very much Dave.

That was exactly the information I needed.

Dave Johnson.
 
Back
Top