INDEX_MATCH not reponding to my liking. Need help

I cannot tell why that is not working. I checked for rounding errors etc. No explanation. However this array-entered form of your formula does work. Go figure.

Formula:

Please Login or Register  to view this content.

If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.

Edit This non-array formula also works.

Formula:

Please Login or Register  to view this content.

the bug/issue
https://support.microsoft.com/en-us/kb/78113

you can fix by putting on precision as displayed

alternatively

Formula:

Please Login or Register  to view this content.

take off the exact in the match

or you can try C16=INDEX($C$5:$CQ$5,MATCH(C15&0,INDEX(C4:CQ4&0,),0))

Formula:

Please Login or Register  to view this content.

@ humdingaling and Shukla.ankur281190

Thanks for the backup and solutions. I've never run across this before.

i've never seen Shukla. method before
its like index inception

Another way would be to use your original INDEX-MATCH and use this in D4 and drag across.

=C4+0.1

All these workarounds are interesting, but does anyone have an actual explanation? Floating-point arithmetic has been suggested, but in what sense does it apply to the value here?

Interestingly, if you go into the cell containing 1.7, i.e. F4, and recommit it with ENTER then the formula works fine. Perhaps this data was imported/pasted from an external source? But then that wouldn't necessarily explain why the value was not being recognised as 1.7 prior to this user intervention (the formula =F4=1.7 returned TRUE).

Regards

I'd just have rounded it to some number of decimal places

Please Login or Register  to view this content.

oh wait that doesn't work... hmm never mind me

I'm not sure how it has arisen at all. I tried, as a first-off, =C15=F4, which returns TRUE. Shukla's answer does deliver, but does does adding any number; or, indeed, any text (using &"Aabracadabra" also works...).

XORLX, have you seent hsi sort of thing before?

or simply:

Formula:

Please Login or Register  to view this content.

Originally Posted by Glenn Kennedy

XORLX, have you seent hsi sort of thing before?

I've seen similar things, in the sense that, for whatever reason, a cell value/formula needs to be re-committed/re-coerced, either by physically going into the cell itself and pressing ENTER, or by passing it to some coercing function (as in all the formula solutions offered so far).

I'm not sure what causes that issue to arise in the first place, though. I would hazard a guess that it's something to do with that value being from an imported source, though that still doesn't explain what the precise issue is, nor why it occurs.

Hopefully someone will be able to shed some light one this!

Regards

If you widen the columns you'll see the items in C4:CG5 are being interpreted by Excel as 'text'.

Originally Posted by snb

If you widen the columns you'll see the items in C4:CG5 are being interpreted by Excel as 'text'.

Really? I recall applying the formula ISNUMBER to all of them and receiving TRUE in all cases.

Regards

enter 1,8 in G3 and compare the alignment to G4.

Originally Posted by snb

enter 1,8 in G3 and compare the alignment to G4.

Sorry - I'm not sure what you mean. As far as I know, ISNUMBER will only ever return TRUE if the value passed is a number. Are you saying otherwise?

Regards

Did you do what I suggested in #17 ?

Originally Posted by snb

Did you do what I suggested in #17 ?

Yes, but I don't understand how that's a rigorous test for numericalness. A user can choose to left-align a number, after all, so right-alignment in itself does not indicate that a value is a number.

However, ISNUMBER always does, which is my point.

Regards

ok done.. check the updated file attached here

what i did was
to copy that table and transpose it at another place so u can easily do rounding up for the cells and also text to column to remove any unidentified characters usually came from other source of program, and then i copy and transpose and paste as value back to your original table.

i also added rounding up formula to your code see below.

Please Login or Register  to view this content.

usually whenever i retrieve tables from other source of program, the text to column method is always a must for value columns.

what you need to do with the text to column is just to select the value column, click the text to column and select maybe delimited and just click finish.