Lengthy Vlookup Values

  • Thread starter Thread starter jkiser
  • Start date Start date
J

jkiser

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?
 
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.
 
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)
 
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.
 
Back
Top