vlookup error - recognition of value
 

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)

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)
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Excell Rookie" <Excell wrote in message
...
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)

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" wrote:

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)
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Excell Rookie" <Excell wrote in message
...
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)

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" wrote in message
...
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.

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

"Excell Rookie" wrote:

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)

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,


"Duke Carey" wrote:

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

"Excell Rookie" wrote:

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)

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.
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--