I’m using the 'match ’ command, column of numbers goes from negative to positive, match fails across the transition.
Is there a workaround for this?
Column of numbers like this, as it tries to look past the boundary where the numbers go from negative to positive, it fails past that point, and gives a #N/A :
-0.31288
-0.25805
-0.20773
-0.16138
-0.11853
-0.07879
-0.04184
-0.00737
0.02485
0.05504
0.08339
0.11006
0.13521
0.15896
Which specific formula on this range gives you the #N/A
=MATCH(($J38-$D$22),$J$37:$J$1036)
The J column is the one I’m indexing with. I figured out that I can create another column that’s smaller by 10 than column J and index against that one instead, since that puts me in a range where I will never practically speaking hit a positive number. So it works, as a kludge, but I’m surprised that it failed when the column went positive. The value in $D$22 is just the amount by which I’m offsetting looking for matches.
hmm… so this gives me nothing. I don’t see D22 or J38+; so I don’t see when (on which value of search criterion) do you get the failure.
Putting the values from your question into cells A1:A14, I successfully get proper answer for, e.g., =MATCH(0.025;A1:A14), which is 9.
D22 has a value of .025, but I’ve tried a range of values, they all fail as the list goes positive and the match command reaches down to that point. The first value I listed was J53, I picked a value lower down where the reach of match was just about going into positive territory. Too bad I can’t upload the spreadsheet or send a screen grab. I found a workaround but it’s inelegant.
Too bad I can’t upload the spreadsheet or send a screen grab
Is that because the data is confidential? If so, could you simply create a new spreadsheet with an excerpt (like the one you pasted to the question), with relevant formulas in a couple of cells?
Is the range $J$37:$J$1036 sorted ascending and has values, not text? If you replace ($J38-$D$22) with just any value bigger than 0.02485, is the result still #N/A? For testing purposes, copy values from J column to another test sheet, copy and sort ascending range $J$37:$J$1036 to another column in test sheet, make sure that you are trying to match same data type (values in values, use View → Value highlightning) and use Match function on the range. And try to add 0 as third Match parameter.
I have a smaller one that was exhibiting the same behavior, but now I’ve done something and it’s working correctly. I’ll have to play with it some more to see what’s going on. It’s all numbers, no text.
Something quirky here. Nothing complicated, or unusual. Just works sometimes and not sometimes. If I subtract 10 from column J in another column, call it K and just change the array reference to that column, it gets me out of trouble functionally even though I haven’t really fixed anything. I’m just going to let it go for now.