vlookup error - recognition of value

E

Excell Rookie

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

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

Excell Rookie

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

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
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
Excell Rookie said:
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
Click to expand...
etc > When I retype these values into my arry, the VLOOKUP functionaly
works.
 
D

Duke Carey

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
 
E

Excel Rookie

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,
 
M

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