MATCH function using non contiguous ranges

Question

I am trying to search from an non contiguous array that is a part of a column of data. I have selected certain values from the column and defined them as an array that I need to be able to search and locate the maximum value using the MATCH function. Here is an example:

I am looking at speed data collected from an interstate. One column indicates whether the vehicle is a car or a truck and the next column lists the speed. I have sorted the data by ascending speed. In the next column I have counted all speed counts that fall between a 10 mph range of any speed value and that value plus 10, this is called the pace. 

I selected all the car data and named two arrays that consist only of car data: carspeed and carpace. I did the same for trucks (truckspeed and truckpace). There are a total of 100 speed counts and they are not sorted by vehicle type, creating non-contiguous ranges. I need to be able to search one of the pace arrays and locate the position of the maximum value using the MATCH function and then the INDEX function.

I think there is a problem when the lookup array for the MATCH function is not contiguous. But I don't want to have to sort my data twice every time I use this. Any suggestions?

Answer 1

I figured out a different way to do it....

Why not share it with those who come to read this thread (both now and via future searches).

Answer 2

Hi,

Enter this array formula (Ctrl+Shift+Enter) in cell R10

=INDEX(Speeds,MAX(MATCH(1,INDEX((Pace)*($B$4:$B$103="C"),,),0)),1)&" - "&INDEX(Speeds,MAX(MATCH(1,INDEX((Pace)*($B$4:$B$103="C"),,),0)),1)+10

Hope this helps.

This formula found the maximum speed in my worksheet but that's not what I'm looking for. Thank though! I figured out a different way to do it but I am still curious; does the match function not work when searching non-contiguous ranges? I don't know why my original formula wasn't working.