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).

I had to copy and paste the non-contiguous range of cells into a new column so that they would be contiguous and then I used the original formula. I wanted to figure out a way to search a non-contiguous range and use the INDEX-MATCH method but it didn't work out. Oh well, done is better than perfect. Thanks!

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.

Answer 3

Also, everything in the middle of the workbook is insignificant. I was just trying different methods of trial and error to attempt to determine the problem. Lastly, the formula in P10 & T10 are slightly different and that too is a result of me trying different methods to solve the problem.

Answer 4

Here is the link:

https://onedrive.live.com/redir?resid=FBE7287AC45EEE77!112&authkey=!AL7sqUsqELEOZnU&ithint=file%2cxls

The cells highlighted in green are the ones I'm working on. R10, T10, and P10 are essentially the same command except that they are using different ranges of cells. Why don't T10 & P10 work? Thanks!

Answer 5

Hi,

Upload your workbook to OneDrive and share the link of the workbook here.