All,

I was trying to use the match function on a non-sorted array. Using the
final option to be 1 and I am getting funny results depending on the
size of the array that I pass ... the array that I using is pasted
below and starts at cell B1 in my worksheet:

6.247097314
7.415613355
8.846348525
10.60682215
12.78346586
15.4868769
18.85815023
23.07605113
28.36417996
34.9960463
43.29364964
53.61114205
66.2889247
81.5557226
99.3510699
119.0507729
139.1278957
156.8977329
168.653679
170.5412546
160.1634496
138.146888
108.4363164
76.80743043
48.54755565
26.82511268
12.41893668
4.386490402
4.386490402
4.386490402

The formula is : Match(125,$b$1:$b$30,1)... this formula leads to 30.
But if I enter the formula as
Match(125,$b$1:$b$29,1) it results in 16 which is the correct value.
Even though I am not adding any new values at the end (the last 3
values in the array are the same) .. I am getting a different answer
each time !

Has this happened to someone before? Any thoughts??

because the "1" at the end of the formula is saying the range IS sorted.
When it's not, the results are pretty unreliable! You should sort this range
first.

wrote in message
oups.com...
All,

I was trying to use the match function on a non-sorted array. Using the
final option to be 1 and I am getting funny results depending on the
size of the array that I pass ... the array that I using is pasted
below and starts at cell B1 in my worksheet:

6.247097314
7.415613355
8.846348525
10.60682215
12.78346586
15.4868769
18.85815023
23.07605113
28.36417996
34.9960463
43.29364964
53.61114205
66.2889247
81.5557226
99.3510699
119.0507729
139.1278957
156.8977329
168.653679
170.5412546
160.1634496
138.146888
108.4363164
76.80743043
48.54755565
26.82511268
12.41893668
4.386490402
4.386490402
4.386490402

The formula is : Match(125,$b$1:$b$30,1)... this formula leads to 30.
But if I enter the formula as
Match(125,$b$1:$b$29,1) it results in 16 which is the correct value.
Even though I am not adding any new values at the end (the last 3
values in the array are the same) .. I am getting a different answer
each time !

Has this happened to someone before? Any thoughts??

Bob,

Thanks for the reply. I do know that it is not sorted and the 1 assumes
that it is sorted... the thing that bugs me is that there are no
additonal different values in there .... and excel does not seem to
give a consistent answer (I dont care if the answer is wrong).... what
bothers me is that the consistency is missing ..

-Naveen

Because you have told the function that it can expect a sorted range it
assumes that it can safely make assumptions about where the value will be.
(There are a few different algorythems it could be using but lets assume it
uses bisection). The first thing that the function will do is look at the
value in the mid point of the list and based on that value it will decide if
the value you are looking for is in the first half or the last half of the
list. By doing this it can very quickly zoom in on the desired value tossing
out half the list and choosing a new mid point with each iteration through
the loop. If you change the size of the array then you change the mid point
and since your mid point is essentailly random the function makes different
(wrong) assumptions with each iteration. That is why you will get seemingly
random results.
--
HTH...

Jim Thomlinson


" wrote:

Bob,

Thanks for the reply. I do know that it is not sorted and the 1 assumes
that it is sorted... the thing that bugs me is that there are no
additonal different values in there .... and excel does not seem to
give a consistent answer (I dont care if the answer is wrong).... what
bothers me is that the consistency is missing ..

-Naveen