# Solved =INDEX Problems with Data

January 17, 2013 at 05:21:49
Specs: Windows 7
 Hi Can anybody help with this problem"=INDEX(\$G\$5:\$G\$11181,MATCH(K6,\$G\$5:\$G\$11181,0))This works except when you enter a new number in K6 it doesnt see it in Col G even though its there. If I cut and paste a number from Col G into K6 it sees the number I have tried =CLEAN but that doesnt workAlso using "=K5=K12 it comes up false even though the number is the same. I cant see anything in the data which is not the sameI have tried to use VLOOKUP and match but that comes up with NA"=VLOOKUP(K5,G5:G14,MATCH(K5,G5:G14,0),FALSE)Many Thanks

See More: =INDEX Problems with Data

#1
January 17, 2013 at 06:11:42
 Sometimes Excel gets so fixated on the format of data in a cell, especially if it sees it as text or if it was downloaded from a website, that it refuses to let it go.If it is truly a number, then multiplying it by 1 or adding 0 will often force Excel to recognize it as a number.Try this:Right Click a blank cell and choose CopySelect the data in Column GRight Click...Paste Special...Operation...AddThere is also a TRIM function, but the Add 0 method requires less steps.Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

#2
January 17, 2013 at 23:51:37
 Hi DerbyDad03Thanks for that, yes you were right it was holding the number as TextAfter a lot of searching I came across this command "=VALUE(AX216) this turns the text into a number,then I cut and paste the col back into my dataMany thanks

Report •

#3
January 18, 2013 at 04:45:34
 Did you try my "add zero” method?I'm curious as to whether it worked. If it does, that would be less steps than your method.Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

Related Solutions

#4
January 18, 2013 at 06:56:59
 Hi I multiplied it by one as u suggested and that worked as well Thanks

Report •

#5
January 18, 2013 at 07:04:53
 Adding zero is even quicker (assuming it works) since you can copy any blank cell, eliminating the need to enter and then delete the 1.Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •