Index & Match probelm

[njsavage]

Hi,

i am having trouble using the index and match functions.

i am working with a spreadsheet wher ei would like to match a number in one col. to find a value in another col with the same row number. i am pasting data into the sheet and the result cells dont seem to work with some data and will with others.

The simple formula

=INDEX($B$2:$B$1002,(MATCH(J2,$A$2:$A$1002,0)))

will index the row number found in the match of col A using the vlaue J2. this number is always returned no matter what the data. the following formula is then used....


=INDEX($B$2:$B$1002,(MATCH(J2-0.02,$A$2:$A$1002,0)))

the variable -0.02 is added to show the difference in values over time. it will decrease to 0.04 then 0.06 etc etc. some of the decrements will work and other will not return the match row number. again, different data will work in some areas but not others.

i can email an example sheet if that would be more helpful..

i have tried increasing the decmial places to 20 to ensure that i am not looking for a number that isnt there. col A doesnt change ever! only col B values will change with different data.

hope you can help
nick

 

[Aladin Akyurek]

Try to post a 5-row sample.