Using match type 1 or -1 with unordered list

[tbw]

I am using match with match type 1 with an unordered list. I know you are supposed to order it, but I cannot. It seems like excel is looking for a portion of the lookup range that is in order and giving a value that is the largest value less than the lookup value inside that portion of the range. The value is not the largest value less than the lookup value for the entire range.


The answer that I get is fine, I would just like to understand what it is doing and if the methodology is consistent.


I think elsewhere on the blog it was mentioned that using match with an unordered list would give the first value that is less than (or greater than) than the lookup value. However, this is not what I get.

 

[NARAYANK991]

Hi ,


You can experiment using MATCH with the following values :


3 7 13 4 6 0 17 23 47 33


Suppose you try =MATCH(5,Above_List,1) , you get 1 ; using =INDEX(Above_List,1) gives the value of 3.


Similarly , trying =MATCH(34,Above_List,1) returns 8 ; using =INDEX(Above_List,8) gives the value of 23.


Now , if you order the above list in ascending order , you have :


0 3 4 6 7 13 17 23 33 47.


Using the same formulae as before , =MATCH(5,Ordered_List,1) returns 3 ; using =INDEX(Ordered_List,3) returns 4.


Similarly , =MATCH(34,Ordered_List,1) returns 9 ; using =INDEX(Ordered_List,9) returns 33.


What is happening is that MATCH starts with the first item of the list ( whether as-is or ordered ) and proceeds downwards , till it comes across an item which exceeds the value to be matched ; then it steps back one item , and returns the value at that position ; in an ordered list , this procedure correctly returns the value just less than the value you are trying to match ; in an unordered list , the result can be any value less than the value you are trying to match.


Narayan

 

[tbw]

Narayan,


Thank for the reply.

You said "What is happening is that MATCH starts with the first item of the list ( whether as-is or ordered ) and proceeds downwards , till it comes across an item which exceeds the value to be matched ; then it steps back one item , and returns the value at that position"


This is not what is happening. Here is my list:

-0.079

0.42

1.204

1.311

0.763

-0.029

0.958

1.693

1.847

1.26

0.419

0.144

0.107

0.357

0.472

0.646

0.673

0.706

0.494

0.351

0.403

0.442

0.512

0.486

0.534

0.467

0.467

0.415

0.524

0.619

1.268


Here's is my formula: = MATCH(1,list,1).

If what you say is true, it should return position 2 (position of '0.42'). Instead, it returns position 30, second to last (position of '0.619').


However, If I shorten the lookup array to the first five items, I do get position 2, as you would expect. The only thing I can think of to explain this is that Excel checks for order in some subset of the list - ?


Teresa

 

[fred]

http://www.techonthenet.com/excel/formulas/match.php


I guess you have to ascend or decend your order when you need to use 1 or -1 in the 3rd string of argument in match function?


So tbw, exactly what was your ideal answer or what you trying to find on your list? if you want to count the number of data less than 1 (as in the case of 1 in the 3rd string in match function), using countif(range,"<1") would give you the proper answer of 25 without sorting the order.

 

[NARAYANK991]

Hi ,


You are right. I checked out some values for the size of the list , and till 18 items , it behaves consistently. It always returns the value 0.42


Beyond 18 , the return value changes ! 19 and 20 items returns one result , 21 returns another , 22 yet another ! It's all very strange.


Narayan

 

[tbw]

Fred,

The answer that it gives me is actually what I want, even thought it is not what you think it would give. What I am doing is this: I want to find where in the list the values begin to rise above 1 (the list I gave above is truncated - values continue to climb, in order, >>1). The values are a bit noisy in the beginning and jump around between <0 and >1. Unfortunately, how long this noisy period lasts and where the values start to take off >1 is not the same each time (I have many columns of data like this).


Narayan,

Thank you for confirming this odd behavior!


I have tried this formula with more than two dozen columns of numbers similar to the one I posted and get the same behavior. Fortunately, I always get the answer I want. If I only I knew why...


Teresa

 

[SirJB7]

Hi, twb!

I tried the examples posted upper and I can't reach to get the same values as indicated for MATCH and INDEX functions: I typed identical values, used identical formulas, but got different results. Very nice the theory but let's focus on the reality of your problem.

Looking at your list (which I copied down three times from A2 and added a column title in A1 to get more values for testing), if your goal is to detect "where in the list the values begin to rise above 1" -and I understand that you want to obtain the first value that passes over 1 being the previous under 1, that's to say only the first over 1 until it descends under 1 again-, you can apply conditional formatting to column A, or the range needed.

Just add use this rule and choose a suitable conditional format:

=SI(A2>1;SI(ESNUMERO(A1);SI(A1<=1;VERDADERO;FALSO);VERDADERO)) -----> in english =IF(A2>1,IF(ISNUMBER(A1),IF(A1<=1,TRUE,FALSE),TRUE))

Apply it to A2:AXXX, where XXX is the range needed, only except A1 cell, and try if that what you need.

File to upload at your disposition if required.

Regards!

 

[tbw]

SirJB7,


Thanks for your reply. Haven't gotten a chance to test this. However, the nature of my problem may have changed and I might not need this solution after all...


Thank you everyone for the input, though!


Teresa

 

[SirJB7]

Hi, tbw!

You're welcome, and hope you can handle the new nature... otherwise ask again.

Regards!