While attempting to use VLOOKUP THE LOOKUP VALUE IS 12345 the column that
has the list of values to be looked at has '12345 so it is not recognized
and I get NA. The table I am working with has the ' attached to all of the
entries in that column. Is there any way I can get rid of the hash mark so
that my lookup will recognize the vale?

On May 14, 1:50 pm, CLEM wrote:
While attempting to use VLOOKUP THE LOOKUP VALUE IS 12345 the column that
has the list of values to be looked at has '12345 so it is not recognized
and I get NA. The table I am working with has the ' attached to all of the
entries in that column. Is there any way I can get rid of the hash mark so
that my lookup will recognize the vale?

One way is to use TEXT(A1,"@") as the lookup_value in the VLOOKUP
function instead of A1 when the lookup_value is in A1.

BTW it's an apostrophe ('), not a hash.

Ken Johnson

Worked great, thanks!!!

"Ken Johnson" wrote:

On May 14, 1:50 pm, CLEM wrote:
While attempting to use VLOOKUP THE LOOKUP VALUE IS 12345 the column that
has the list of values to be looked at has '12345 so it is not recognized
and I get NA. The table I am working with has the ' attached to all of the
entries in that column. Is there any way I can get rid of the hash mark so
that my lookup will recognize the vale?

One way is to use TEXT(A1,"@") as the lookup_value in the VLOOKUP
function instead of A1 when the lookup_value is in A1.

BTW it's an apostrophe ('), not a hash.

Ken Johnson

On May 17, 12:00 pm, CLEM wrote:
Worked great, thanks!!!

"Ken Johnson" wrote:
On May 14, 1:50 pm, CLEM wrote:
While attempting to use VLOOKUP THE LOOKUP VALUE IS 12345 the column that
has the list of values to be looked at has '12345 so it is not recognized
and I get NA. The table I am working with has the ' attached to all of the
entries in that column. Is there any way I can get rid of the hash mark so
that my lookup will recognize the vale?

One way is to use TEXT(A1,"@") as the lookup_value in the VLOOKUP
function instead of A1 when the lookup_value is in A1.

BTW it's an apostrophe ('), not a hash.

Ken Johnson

You're welcome CLEM.
Thanks for the feedback.

Ken Johnson

Thye say if you can't bring Muhamed to the mountain, bring the mountain to
Muhamed. The hash mark is a single quote used to make the cell string data
instead of a number. Why don't you look up the number with the quote.

from
=VLookup(A1, A1:D100,3)

=VLookup("'" & A1, A1:D100,3)

The first three character are
Double Quote
Single Quote
Double Quote

Basically the change adds a single quote to the beginning of A1. I simply
add the single quote to the search string rather than remove it from the
lookup table.


"CLEM" wrote:

While attempting to use VLOOKUP THE LOOKUP VALUE IS 12345 the column that
has the list of values to be looked at has '12345 so it is not recognized
and I get NA. The table I am working with has the ' attached to all of the
entries in that column. Is there any way I can get rid of the hash mark so
that my lookup will recognize the vale?

Thank you this worked great also........

"Joel" wrote:

Thye say if you can't bring Muhamed to the mountain, bring the mountain to
Muhamed. The hash mark is a single quote used to make the cell string data
instead of a number. Why don't you look up the number with the quote.

from
=VLookup(A1, A1:D100,3)

=VLookup("'" & A1, A1:D100,3)

The first three character are
Double Quote
Single Quote
Double Quote

Basically the change adds a single quote to the beginning of A1. I simply
add the single quote to the search string rather than remove it from the
lookup table.


"CLEM" wrote:

While attempting to use VLOOKUP THE LOOKUP VALUE IS 12345 the column that
has the list of values to be looked at has '12345 so it is not recognized
and I get NA. The table I am working with has the ' attached to all of the
entries in that column. Is there any way I can get rid of the hash mark so
that my lookup will recognize the vale?