EXCEL Wild Cards

[NPVSRUS]

I'm having a problem with wild cards (*,?,!) when doing the vlookup function.
Unfortunately, my comparison data contains these characters so I end up
picking up the wrong data most of the time. Is there any way to turn these
wild cards off?

Thank you in advance,

Scott

 

[Dave Peterson]

My favorite!

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

! isn't a wildcard.

~ is used as an "escape" character to mean the next character shouldn't be
treated as a wildcard.

 

[NPVSRUS]

Thank You Sir!

My favorite!

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

! isn't a wildcard.

~ is used as an "escape" character to mean the next character shouldn't be
treated as a wildcard.