I'm using the VLOOKUP formula and I cannot determine why there is no return
on certain records, which should have a return.

For instance, I execute a macro to perform the VLOOKUP on one list, which
entries like: (TEXT)(SPACE)&(SPACE)(TEXT). The list I am cross referencing
has the exact same listing (ie I check =LEN and they match, I compared via an
IF statement and they match), yet I get #NA. The listing are text and the
range_lookup is not valued in the formula, so my thought is that the "&"
character may be the issue.

So, Are there any characters that cannot be used in combination with the
VLOOKUP formula - If yes, is there a site which I can obtain the list?

Any and all help will be appreciated - Thank You

I've never seen any problem using any characters in =vlookup().

But I have seen the N/A error when there isn't a match.

You checked to see if the lengths were the same. How about just checking to see
if the values are the same:

=a1=sheet2!a99

Adjust the addresses to match.

I'm wondering if the space character in one cell is reall the plain old space
character--maybe it's the HTML non-breaking space (did you grab the data from
the web???).

Chip Pearson has a very nice addin that will help determine what's in the cell:
http://www.cpearson.com/excel/CellView.aspx

MWS-C360 wrote:

I'm using the VLOOKUP formula and I cannot determine why there is no return
on certain records, which should have a return.

For instance, I execute a macro to perform the VLOOKUP on one list, which
entries like: (TEXT)(SPACE)&(SPACE)(TEXT). The list I am cross referencing
has the exact same listing (ie I check =LEN and they match, I compared via an
IF statement and they match), yet I get #NA. The listing are text and the
range_lookup is not valued in the formula, so my thought is that the "&"
character may be the issue.

So, Are there any characters that cannot be used in combination with the
VLOOKUP formula - If yes, is there a site which I can obtain the list?

Any and all help will be appreciated - Thank You

--

Dave Peterson

All the entries in the entire dataset are systematically TRImmed via an excel
formula, I also then systematically copy the range and use paste
special/values to populated the vlookup components, yet no other return other
than #NA. I manually executed the steps within the macro, yet no different
outcome.

I too have not ever had any issues with using characters when using vlookup,
but have been gone crazy looking at the two strings, that seem identical, yet
are not being picked up via vlookup.

Unfortunately, I will not be able to load the addin, due to our restrictive
policy.

Thanks for the response though.
"Dave Peterson" wrote:

I've never seen any problem using any characters in =vlookup().

But I have seen the N/A error when there isn't a match.

You checked to see if the lengths were the same. How about just checking to see
if the values are the same:

=a1=sheet2!a99

Adjust the addresses to match.

I'm wondering if the space character in one cell is reall the plain old space
character--maybe it's the HTML non-breaking space (did you grab the data from
the web???).

Chip Pearson has a very nice addin that will help determine what's in the cell:
http://www.cpearson.com/excel/CellView.aspx

MWS-C360 wrote:

I'm using the VLOOKUP formula and I cannot determine why there is no return
on certain records, which should have a return.

For instance, I execute a macro to perform the VLOOKUP on one list, which
entries like: (TEXT)(SPACE)&(SPACE)(TEXT). The list I am cross referencing
has the exact same listing (ie I check =LEN and they match, I compared via an
IF statement and they match), yet I get #NA. The listing are text and the
range_lookup is not valued in the formula, so my thought is that the "&"
character may be the issue.

So, Are there any characters that cannot be used in combination with the
VLOOKUP formula - If yes, is there a site which I can obtain the list?

Any and all help will be appreciated - Thank You

--

Dave Peterson
.

What happened when you tried a formula that pointed at the lookup value and the
cell you KNOW matched--like my example:

=a1=sheet2!a99

Since your values are only 4 characters long, you could examine each character
with 4 formulas like:

=CODE(MID(A1,1,1))
=CODE(MID(A1,1,1))
=CODE(MID(A1,1,1))
=CODE(MID(A1,1,1))


MWS-C360 wrote:

All the entries in the entire dataset are systematically TRImmed via an excel
formula, I also then systematically copy the range and use paste
special/values to populated the vlookup components, yet no other return other
than #NA. I manually executed the steps within the macro, yet no different
outcome.

I too have not ever had any issues with using characters when using vlookup,
but have been gone crazy looking at the two strings, that seem identical, yet
are not being picked up via vlookup.

Unfortunately, I will not be able to load the addin, due to our restrictive
policy.

Thanks for the response though.
"Dave Peterson" wrote:

I've never seen any problem using any characters in =vlookup().

But I have seen the N/A error when there isn't a match.

You checked to see if the lengths were the same. How about just checking to see
if the values are the same:

=a1=sheet2!a99

Adjust the addresses to match.

I'm wondering if the space character in one cell is reall the plain old space
character--maybe it's the HTML non-breaking space (did you grab the data from
the web???).

Chip Pearson has a very nice addin that will help determine what's in the cell:
http://www.cpearson.com/excel/CellView.aspx

MWS-C360 wrote:

I'm using the VLOOKUP formula and I cannot determine why there is no return
on certain records, which should have a return.

For instance, I execute a macro to perform the VLOOKUP on one list, which
entries like: (TEXT)(SPACE)&(SPACE)(TEXT). The list I am cross referencing
has the exact same listing (ie I check =LEN and they match, I compared via an
IF statement and they match), yet I get #NA. The listing are text and the
range_lookup is not valued in the formula, so my thought is that the "&"
character may be the issue.

So, Are there any characters that cannot be used in combination with the
VLOOKUP formula - If yes, is there a site which I can obtain the list?

Any and all help will be appreciated - Thank You

--

Dave Peterson
.


--

Dave Peterson

Oops. Hit the send too fast.

You could use 4 formulas like:

=CODE(MID(A1,1,1))
=CODE(MID(A1,2,1))
=CODE(MID(A1,3,1))
=CODE(MID(A1,4,1))

Then do the same with the "matching" cell. I'll bet dollars to doughnuts that
you'll find a difference.



MWS-C360 wrote:

All the entries in the entire dataset are systematically TRImmed via an excel
formula, I also then systematically copy the range and use paste
special/values to populated the vlookup components, yet no other return other
than #NA. I manually executed the steps within the macro, yet no different
outcome.

I too have not ever had any issues with using characters when using vlookup,
but have been gone crazy looking at the two strings, that seem identical, yet
are not being picked up via vlookup.

Unfortunately, I will not be able to load the addin, due to our restrictive
policy.

Thanks for the response though.
"Dave Peterson" wrote:

I've never seen any problem using any characters in =vlookup().

But I have seen the N/A error when there isn't a match.

You checked to see if the lengths were the same. How about just checking to see
if the values are the same:

=a1=sheet2!a99

Adjust the addresses to match.

I'm wondering if the space character in one cell is reall the plain old space
character--maybe it's the HTML non-breaking space (did you grab the data from
the web???).

Chip Pearson has a very nice addin that will help determine what's in the cell:
http://www.cpearson.com/excel/CellView.aspx

MWS-C360 wrote:

I'm using the VLOOKUP formula and I cannot determine why there is no return
on certain records, which should have a return.

For instance, I execute a macro to perform the VLOOKUP on one list, which
entries like: (TEXT)(SPACE)&(SPACE)(TEXT). The list I am cross referencing
has the exact same listing (ie I check =LEN and they match, I compared via an
IF statement and they match), yet I get #NA. The listing are text and the
range_lookup is not valued in the formula, so my thought is that the "&"
character may be the issue.

So, Are there any characters that cannot be used in combination with the
VLOOKUP formula - If yes, is there a site which I can obtain the list?

Any and all help will be appreciated - Thank You

--

Dave Peterson
.


--

Dave Peterson

What happened when you tried Dave's suggestion of:
=a1=sheet2!a99
?

Regards,
Fred

"MWS-C360" wrote in message
...
All the entries in the entire dataset are systematically TRImmed via an
excel
formula, I also then systematically copy the range and use paste
special/values to populated the vlookup components, yet no other return
other
than #NA. I manually executed the steps within the macro, yet no different
outcome.

I too have not ever had any issues with using characters when using
vlookup,
but have been gone crazy looking at the two strings, that seem identical,
yet
are not being picked up via vlookup.

Unfortunately, I will not be able to load the addin, due to our
restrictive
policy.

Thanks for the response though.
"Dave Peterson" wrote:

I've never seen any problem using any characters in =vlookup().

But I have seen the N/A error when there isn't a match.

You checked to see if the lengths were the same. How about just checking
to see
if the values are the same:

=a1=sheet2!a99

Adjust the addresses to match.

I'm wondering if the space character in one cell is reall the plain old
space
character--maybe it's the HTML non-breaking space (did you grab the data
from
the web???).

Chip Pearson has a very nice addin that will help determine what's in the
cell:
http://www.cpearson.com/excel/CellView.aspx

MWS-C360 wrote:

I'm using the VLOOKUP formula and I cannot determine why there is no
return
on certain records, which should have a return.

For instance, I execute a macro to perform the VLOOKUP on one list,
which
entries like: (TEXT)(SPACE)&(SPACE)(TEXT). The list I am cross
referencing
has the exact same listing (ie I check =LEN and they match, I compared
via an
IF statement and they match), yet I get #NA. The listing are text and
the
range_lookup is not valued in the formula, so my thought is that the
"&"
character may be the issue.

So, Are there any characters that cannot be used in combination with
the
VLOOKUP formula - If yes, is there a site which I can obtain the list?

Any and all help will be appreciated - Thank You

--

Dave Peterson
.

Thank You, I'll give it a try.

"Dave Peterson" wrote:

Oops. Hit the send too fast.

You could use 4 formulas like:

=CODE(MID(A1,1,1))
=CODE(MID(A1,2,1))
=CODE(MID(A1,3,1))
=CODE(MID(A1,4,1))

Then do the same with the "matching" cell. I'll bet dollars to doughnuts that
you'll find a difference.



MWS-C360 wrote:

All the entries in the entire dataset are systematically TRImmed via an excel
formula, I also then systematically copy the range and use paste
special/values to populated the vlookup components, yet no other return other
than #NA. I manually executed the steps within the macro, yet no different
outcome.

I too have not ever had any issues with using characters when using vlookup,
but have been gone crazy looking at the two strings, that seem identical, yet
are not being picked up via vlookup.

Unfortunately, I will not be able to load the addin, due to our restrictive
policy.

Thanks for the response though.
"Dave Peterson" wrote:

I've never seen any problem using any characters in =vlookup().

But I have seen the N/A error when there isn't a match.

You checked to see if the lengths were the same. How about just checking to see
if the values are the same:

=a1=sheet2!a99

Adjust the addresses to match.

I'm wondering if the space character in one cell is reall the plain old space
character--maybe it's the HTML non-breaking space (did you grab the data from
the web???).

Chip Pearson has a very nice addin that will help determine what's in the cell:
http://www.cpearson.com/excel/CellView.aspx

MWS-C360 wrote:

I'm using the VLOOKUP formula and I cannot determine why there is no return
on certain records, which should have a return.

For instance, I execute a macro to perform the VLOOKUP on one list, which
entries like: (TEXT)(SPACE)&(SPACE)(TEXT). The list I am cross referencing
has the exact same listing (ie I check =LEN and they match, I compared via an
IF statement and they match), yet I get #NA. The listing are text and the
range_lookup is not valued in the formula, so my thought is that the "&"
character may be the issue.

So, Are there any characters that cannot be used in combination with the
VLOOKUP formula - If yes, is there a site which I can obtain the list?

Any and all help will be appreciated - Thank You

--

Dave Peterson
.


--

Dave Peterson
.