2

I am using a vlookup statement: =VLOOKUP(B1232,Sheet1!A:B,2,0). The cell in B1232 contains the string:

'You Rawk!!~'

With the "'" inside the string that I want to go and find, the program retursn #N/A. I believe that the Vlookup command is omitting the opening single-quote when it runs the search, is that true? Is there any way to work around this so that I can run the VLOOKUP?

billmanH
  • 1,298
  • 1
  • 14
  • 25

1 Answers1

4

I don't think the quote is the problem - Excel uses "~" [tilde] as an "escape character" so it has a problem with values that contain "~". You can use a SUBSTITUTE function within your VLOOKUP to replace "~" with "~~" - when using two tildes the first one tells excel to treat the second as a literal "~", i.e. use

=VLOOKUP(SUBSTITUTE(B1232,"~","~~"),Sheet1!A:B,2,0)

That will work whether B1232 contains "~" or not

barry houdini
  • 45,615
  • 8
  • 63
  • 81
  • Worked for me. Notice it failed initially because I had to [recalculate the value of the cells](https://stackoverflow.com/a/40976113/523742). – Tiago Cardoso May 08 '19 at 07:33