GETTING RID OF LEADING HASH MARKS
 

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?

GETTING RID OF LEADING HASH MARKS
 

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

GETTING RID OF LEADING HASH MARKS
 

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?

GETTING RID OF LEADING HASH MARKS
 

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

GETTING RID OF LEADING HASH MARKS
 

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?

GETTING RID OF LEADING HASH MARKS
 

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