VLOOKUP Question

M

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
 
D

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
 
M

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

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

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

Fred Smith

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

Regards,
Fred
 
M

MWS-C360

Thank You, I'll give it a try.

Dave Peterson said:
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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top