vlookup error - recognition of value

[Guest]

I was having issues with Vlookup functionality in a 2 column array. It does
not recognize the value I have in the array intil I over type the value and
then the vlookup functionality works.

My equation looks like this: =VLOOKUP(A2,$J$2:$K$5815,2,FALSE)

 

[Max]

If the lookup value is numeric, try:
=VLOOKUP(A2+0,$J$2:$K$5815,2,FALSE)

If the lookup value is text, try:
=VLOOKUP(TRIM(A2),$J$2:$K$5815,2,FALSE)

 

[Guest]

Thank You for the quick response, niether proposal has remedied the issue.
I am using data which is a "mix" of alpha, and numerical like AE61, ST04 etc

When I retype these values into my arry, the VLOOKUP functionaly works.

 

[Max]

Then the problem is with the table array which probably contains extraneous
leading and/or trailing spaces. Try this on a spare copy of your sheet ..

Using 2 empty adjacent columns ..
Put in say, L2: =TRIM(J2)
Copy across to M2, fill down to M5815

Then select L2:M5815, and do a copy > paste special > values to overwrite
the original table array in J2:K5815

--

Thank You for the quick response, niether proposal has remedied the issue.
I am using data which is a "mix" of alpha, and numerical like AE61, ST04

etc > When I retype these values into my arry, the VLOOKUP functionaly
works.

 

[Guest]

As an alternative to Max's suggestions, copy a completely empty cell, select
the first column of your lookup array, and use Edit-Paste Special-Add. Taht
will convert all the array values to numeric and your VLOOKUP will work then

 

[Guest]

I think Excel was having issues with a dual alpha-numeric arrays and have
resolved my issue by copying data into another colunm, trimming the data
using =trim(h2) and then in an adjacent column copy the value. The formula
now works.

Best Regards,

 

[Max]

Sorry, that probably overdid it .. Just clean up the lookup col J will do,
with the TRIM down col L only, then a copy > paste as values to overwrite
col J.