MATCH last or largest

[Josie Dethiers]

Could someone tells me why the formula
=INDEX(A:A,MATCH(9.99999999999E+307,A:A,1),1)
returns the LAST value in a column instead of returning the LARGEST value ?

What I do not understand is the Type parameter of the MATCH function.
My French Excel help file tells me that, with a value of 1, MATCH is
supposed to find the biggest value smaller or equal to the value to be found
and that the values to be searched must be sorted in ascending order.

But the formula INDEX(...;EQUIV(...);...) returns the LAST value in the
column. That's what I want but I would like to know how it works. Since my
data are not sorted in ascending order, I would have thought that the
formula would return #NA as it does with the -1 type parameter when the
cells are not sorted in descending order.

Josie

 

[Harlan Grove]

Could someone tells me why the formula
=INDEX(A:A,MATCH(9.99999999999E+307,A:A,1),1)
returns the LAST value in a column instead of returning the LARGEST value ?

What I do not understand is the Type parameter of the MATCH function.
My French Excel help file tells me that, with a value of 1, MATCH is
supposed to find the biggest value smaller or equal to the value to be found
and that the values to be searched must be sorted in ascending order.

...

This could be called a bug in the way MATCH works when given 1 as third
argument. However, it's a useful bug. Also, this isn't the only place in which
Excel's documentation is inadequate or inaccurate.

A guess only: Excel's MATCH function first tries to bracket the lookup value
(MATCH's first argument) with two values in the lookup range/array (MATCH's
second argument). When looking up 9.99999999999E+307, it won't find a higher
value, but it continues to search downwards or rightwards through the
range/array until it runs out of data, since it assumes the range/array is
sorted in ascending order. When it runs out of data, it assumes the last
(furthest downwards or rightwards) value is the largest value less than or equal
to the lookup value. Aside from determining the direction to continue searching,
MATCH doesn't rely strictly upon the range/array being sorted unless it's found
both higher and lower (so bracketting) values in the range/array, at which point
it uses binary search to locate the largest value less than or equal to the
lookup value.

 

[Josie Dethiers]

Thank you Harlan

This could be called a bug in the way MATCH works when given 1 as third
argument. However, it's a useful bug. Also, this isn't the only place in which
Excel's documentation is inadequate or inaccurate.

Yes. I learned to read it between the lines. Usually, what is the most
interesting in this doc is what is untold.

Josie