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


See More: =INDEX Problems with Data

Report •


#1
January 17, 2013 at 06:11:42
✔ Best Answer

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

There 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 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


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 •


Ask Question