CODEWITHSUNDEEP
stringexcelvlookup
billmanH
billmanH

Reputation: 1426

Vlookup returns #N/A if string contains a '

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?

Upvotes: 3

Views: 2314

Answers (1)

barry houdini
barry houdini

Reputation: 46361

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

Upvotes: 5

Related Questions