MATCH function workaround/documentation

In the MATCH() function, it is unclear what Excel does with duplicate data. For example, I have a time series of data and want to determine when it reached a given value. t = 0,1,2,3,4,5,6, ... value = 1,2,2,3,3,3,4, ..... These values are in "ascending order" so I use =MATCH(3, array, 1), but it does not find the earliest time it reached 3, which would be the 4th point, but reports the last time, which in this example would be the 6th point.

Any ideas how to get this time? Our data is ascending but does not have great precision, so some values are identical. Thanks

Change the 1 at the end of the formula to zero:

=MATCH(3, array, 0)

This will find the first value that matches 3.

Hope this helps.

Pete

I tried to reply twice yesterday, but seemed to be having internet connection problems. In short, I could not use the exact match because my data has gaps (unlike my example). The data might read 0,1,2,2,4,5,5,... and I want to look to when it passed 3, for example.

The solution was to search for a number smaller than the desired threshold and then add 1, so MATCH(2,array,1)+1 would give me the correct index for when it reached or exceeded 3. I have to tweak this for my data precision, but that is the idea that worked.

Okay, glad you found a solution - thanks for feeding back.

If that takes care of your original question, please take a moment to select Thread Tools from the menu above your first post and mark this thread as SOLVED.

Pete