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 work
Also 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 same
I 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
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 Copy
Select the data in Column G
Right 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.
Hi DerbyDad03 Thanks for that, yes you were right it was holding the number as Text
After 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 data
Many thanks
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.
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.
