I'm using the 'match ' command, column of numbers goes from negative to positive, match fails across the transition.

asked 2018-08-13 17:25:15 +0200

this post is marked as community wiki

This post is a wiki. Anyone with karma >75 is welcome to improve it.

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

edit retag flag offensive close merge delete

Comments

Which specific formula on this range gives you the #N/A

Mike Kaganski gravatar imageMike Kaganski ( 2018-08-13 17:46:30 +0200 )edit

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

mlytle0 gravatar imagemlytle0 ( 2018-08-13 19:36:44 +0200 )edit

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.

Mike Kaganski gravatar imageMike Kaganski ( 2018-08-13 20:13:56 +0200 )edit

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.

mlytle0 gravatar imagemlytle0 ( 2018-08-13 20:46:06 +0200 )edit

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?

Mike Kaganski gravatar imageMike Kaganski ( 2018-08-13 20:52:32 +0200 )edit

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.

SM_Riga gravatar imageSM_Riga ( 2018-08-13 22:06:21 +0200 )edit

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.

mlytle0 gravatar imagemlytle0 ( 2018-08-13 22:12:49 +0200 )edit

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.

mlytle0 gravatar imagemlytle0 ( 2018-08-13 22:20:36 +0200 )edit