 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Worksheet Functions / September 2005vlookup 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 -- > 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. > If the lookup value is numeric, try: > =VLOOKUP(A2+0,$J$2:$K$5815,2,FALSE) [quoted text clipped - 17 lines] > > > > 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 -- > 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. 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 -- 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 > 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, > 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 [quoted text clipped - 5 lines] > > > > My equation looks like this: =VLOOKUP(A2,$J$2:$K$5815,2,FALSE) |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.