Vlookup for text that contains quotes

[pajordan]

How can I used the vlookup function if the "lookup value" I'm trying to find in a range of data contains quotes, ex.:
Fluorescent, (1) 18" T12 lamp

Let's say that lookup value is in cell O10, my table array is in $A$!:$H$!100, the column index is 7; I tried this formula first:
=VLOOKUP(O10,$A$1:$H$100,7,FALSE)
and I get the #N/A error.

I was going to use the substitute function to replace the " with a ? (single character wildcard), so I tried:
=VLOOKUP(SUBSTITUTE(O16, """, "?"),$A$1:$H$140,7,FALSE)
but that won't work either.

Any suggestions, please? Much appreciated.

[ajryan88]

Hi,

There is no problem using a VLOOKUP on a value containing quotes...so I'd suggest there's something else fishy going on. The reason a VLOOKUP would return #N/A is because the value isn't found.

There may be trailing or leading spaces, which can be eliminated using the TRIM function.

If this doesn't work, can you please upload your workbook (or a small sample of it) so that I can take a look into this problem for you?

Thanks

[pajordan]

Hi ajryan88,

Yes, you are correct - I had the table array set up wrong (I forgot that the lookup value needs to be somewhere in the first column of the table array, and not just any column - oops).

Incidentally, I found that if I wanted to type out the lookup value (instead of referencing the cell containing it) I could use the char(34) function to represent the ", like this:
=VLOOKUP(CONCATENATE("Fluorescent, (1) 18" & CHAR(34) & " T12 lamp"),$F$1:$H$141,2,FALSE)

Thanks for your response!

[ajryan88]

Hi,

That's correct, and to simplify it even further, you can just use "" within existing quotes to give you a ", so it would become

Please Login or Register  to view this content.

Please don't forget to mark this thread as solved and please click on the * next to my post(s) to say thanks