Lengthy Vlookup Values
 

I have formula:

=VLOOKUP($A11, Target Array,12,False), I copy it down and it works
great....usually.

Occassionally I run into an entry in column A that is long and it won't
work. For instance the entry:

%,NCOMPENSATION Then the formula doesn't return a value.

But if I change the entry by removing the last letter both the column and
the target array:

%,NCOMPENSATIO Then it does return a value (the correct one).

That is happening whenever I find an entry over 14 characters. Is there a
limitation of some sort? Is there a workaround?

Lengthy Vlookup Values
 

Correction....it appear that this is not a lenght sensitivity issue. There
are some entries that simply aren't getting picked up unless I remove the
last letter regardless of length. I've tried everything....I'm stumped.

"jkiser" wrote:

I have formula:

=VLOOKUP($A11, Target Array,12,False), I copy it down and it works
great....usually.

Occassionally I run into an entry in column A that is long and it won't
work. For instance the entry:

%,NCOMPENSATION Then the formula doesn't return a value.

But if I change the entry by removing the last letter both the column and
the target array:

%,NCOMPENSATIO Then it does return a value (the correct one).

That is happening whenever I find an entry over 14 characters. Is there a
limitation of some sort? Is there a workaround?

Lengthy Vlookup Values
 

Try

=VLOOKUP($A11 & "*", Target Array,12,False)

If this post helps click Yes
---------------
Jacob Skaria


"jkiser" wrote:

Correction....it appear that this is not a lenght sensitivity issue. There
are some entries that simply aren't getting picked up unless I remove the
last letter regardless of length. I've tried everything....I'm stumped.

"jkiser" wrote:

I have formula:

=VLOOKUP($A11, Target Array,12,False), I copy it down and it works
great....usually.

Occassionally I run into an entry in column A that is long and it won't
work. For instance the entry:

%,NCOMPENSATION Then the formula doesn't return a value.

But if I change the entry by removing the last letter both the column and
the target array:

%,NCOMPENSATIO Then it does return a value (the correct one).

That is happening whenever I find an entry over 14 characters. Is there a
limitation of some sort? Is there a workaround?

Lengthy Vlookup Values
 

You're well within the length limit. It's 255 characters.

There may be unseen characters like spaces in you table data. For example,
you want to lookup "xxx" but in your table that entry is "xxx<space"

Find an entry that isn't working and check that the length of the lookup
value and its matching value in the table are the same.

A1 = lookup value = xxx

Assume its matching value in the table is in cell B10.

Try these formulas to compare the two values:

=A1=B10
=LEN(A1)=LEN(B10)

--
Biff
Microsoft Excel MVP


"jkiser" wrote in message
...
Correction....it appear that this is not a lenght sensitivity issue.
There
are some entries that simply aren't getting picked up unless I remove the
last letter regardless of length. I've tried everything....I'm stumped.

"jkiser" wrote:

I have formula:

=VLOOKUP($A11, Target Array,12,False), I copy it down and it works
great....usually.

Occassionally I run into an entry in column A that is long and it won't
work. For instance the entry:

%,NCOMPENSATION Then the formula doesn't return a value.

But if I change the entry by removing the last letter both the column and
the target array:

%,NCOMPENSATIO Then it does return a value (the correct one).

That is happening whenever I find an entry over 14 characters. Is there
a
limitation of some sort? Is there a workaround?

Lengthy Vlookup Values
 

I found the problem....by changing the dimensions of the array and sometimes
it worked and other times it didn't. I kept changing it until I found the
culprit....the value I was searching on was repeated higher up in the array.

I think I can work around the problem if I use a relative reference for the
array.

Your ideas are really good though because I run into this now and again.
Thanks so much.


"T. Valko" wrote:

You're well within the length limit. It's 255 characters.

There may be unseen characters like spaces in you table data. For example,
you want to lookup "xxx" but in your table that entry is "xxx<space"

Find an entry that isn't working and check that the length of the lookup
value and its matching value in the table are the same.

A1 = lookup value = xxx

Assume its matching value in the table is in cell B10.

Try these formulas to compare the two values:

=A1=B10
=LEN(A1)=LEN(B10)

--
Biff
Microsoft Excel MVP


"jkiser" wrote in message
...
Correction....it appear that this is not a lenght sensitivity issue.
There
are some entries that simply aren't getting picked up unless I remove the
last letter regardless of length. I've tried everything....I'm stumped.

"jkiser" wrote:

I have formula:

=VLOOKUP($A11, Target Array,12,False), I copy it down and it works
great....usually.

Occassionally I run into an entry in column A that is long and it won't
work. For instance the entry:

%,NCOMPENSATION Then the formula doesn't return a value.

But if I change the entry by removing the last letter both the column and
the target array:

%,NCOMPENSATIO Then it does return a value (the correct one).

That is happening whenever I find an entry over 14 characters. Is there
a
limitation of some sort? Is there a workaround?

Lengthy Vlookup Values
 

Good deal. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"jkiser" wrote in message
...
I found the problem....by changing the dimensions of the array and
sometimes
it worked and other times it didn't. I kept changing it until I found the
culprit....the value I was searching on was repeated higher up in the
array.

I think I can work around the problem if I use a relative reference for
the
array.

Your ideas are really good though because I run into this now and again.
Thanks so much.


"T. Valko" wrote:

You're well within the length limit. It's 255 characters.

There may be unseen characters like spaces in you table data. For
example,
you want to lookup "xxx" but in your table that entry is "xxx<space"

Find an entry that isn't working and check that the length of the lookup
value and its matching value in the table are the same.

A1 = lookup value = xxx

Assume its matching value in the table is in cell B10.

Try these formulas to compare the two values:

=A1=B10
=LEN(A1)=LEN(B10)

--
Biff
Microsoft Excel MVP


"jkiser" wrote in message
...
Correction....it appear that this is not a lenght sensitivity issue.
There
are some entries that simply aren't getting picked up unless I remove
the
last letter regardless of length. I've tried everything....I'm
stumped.

"jkiser" wrote:

I have formula:

=VLOOKUP($A11, Target Array,12,False), I copy it down and it works
great....usually.

Occassionally I run into an entry in column A that is long and it
won't
work. For instance the entry:

%,NCOMPENSATION Then the formula doesn't return a value.

But if I change the entry by removing the last letter both the column
and
the target array:

%,NCOMPENSATIO Then it does return a value (the correct one).

That is happening whenever I find an entry over 14 characters. Is
there
a
limitation of some sort? Is there a workaround?