VLOOKUP Question

[MWS-C360]

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]

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]

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]

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))

 

[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.

 

[Fred Smith]

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

Regards,
Fred

 

[MWS-C360]

Thank You, I'll give it a try.

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.