That's great! About the explanation...I'm not sure how much of this is already known, so I'll give a fairly complete overview---please bear with me. All of the formulas that pull information from your 'Raw Data' sheet are nearly identical, differing substantively only in the first argument of the INDEX function. For example, in cell A4 of the the #10 sheet, we have:
=IFERROR(INDEX('Raw Data'!$B$2:$B$20,SMALL(IF($A$2='Raw Data'!$A$2:$A$20,ROW('Raw Data'!$A$2:$A$20)-1,""),ROWS($A$4:A4))),"")
I'll focus on the middle and end part of the formula: IF( $A$2='Raw Data'!$A$2:$A$20, ROW('Raw Data'!$A$2:$A$20)-1, "")
The IF function evaluates the 1st argument and if the result is "TRUE", then the 2nd argument is processed by the function, otherwise the 3rd argument is processed. This IF statement is used to construct an array of list positions where vehicle id #10 (the value in A2) can be found on the range 'Raw Data'!A2:A20 (which lists vehicle ID numbers). To accomplish this, the function performs an equality test on each element in the 'Raw Data'!$A$2:$A$20 range to determine if it matches the value in A2 on the current #10 worksheet. The resulting array consists of TRUE's and FALSE's. You can see this by clicking on the A4 cell, and then in the formula bar, select $A$2='Raw Data'!$A$2:$A$20 and hit F9...Excel will show the array resulting from this series of logical tests. Be sure to hit Esc after taking this peek, otherwise, that part of the formula can be hard-coded with the array. The reason this array is generated is because the formula is entered as an array formula, which instructs Excel to execute the formula repeatedly for each item in the range, essentially making a row-by-row comparison.
The TRUE's and FALSE's are then processed by the IF statement: FALSE entries trigger the return of the 3rd argument in the IF statement, which happens to be an empty string ""; while TRUE entries cause the 2nd argument to be delivered, specifically ROW('Raw Data'!$A$2:$A$20)-1
The ROW function returns the row number or numbers corresponding to its argument, so ROW(A2:A20) would return {2;3;4;...;19;20}. However, since this ROW function is woven into the "TRUE" part of the IF function, the row number of only those cells in 'Raw Data'!$A$2:$A$20 that match the vehicle id # will be incorporated into the final array. Any FALSE array elements (corresponding to non-matching cells) will be replaced with an empty string "". Ultimately we do not want the row numbers, but rather the list positions in the data block, and since data begin on row 2, we subtract the number of the row just before the data block (in this case "1"). You might encounter similar constructions such as ROW(C$5:C$20)-ROW(C$4). This form includes a hint that the row correction is due to the position of data on the sheet and the subtracted term often renders a value that is one less than the row where data begin. In this example, we end up with the array {1;"";3;"";...;""}, meaning that the 1st and 3rd elements in the 'Raw Data'!$A$2:$A$20 range match vehicle id# 10. Again the select-F9-view-Esc trick can be used to see this intermediate result, assuming that the entire IF statement, and only the entire IF statement, is selected.
This array is used within the INDEX function to return corresponding data in other columns on the 'Raw Data' sheet. So how do we control which of the array elements should be processed so that the same list position is not processed twice or missed altogether? This is the purpose of the SMALL function. To simplify the appearance of the function, I've swapped out the IF function for the result that it gives and inserted some extra spaces:
SMALL( {1;"";3;"";...;""} , ROWS($A$4:A4) )
There are two arguments: the 1st is the list of values to select from (the list positions); the 2nd is a number describing whether to return the smallest value in that list (the "1"st smallest), the next smallest (the "2"nd smallest), etc. We need some way to generate these values of 1,2,...and so on, to ensure that all of the non-empty items in the array are returned one at a time. In this case, I used ROWS($A$4:A4) to accomplish this. Note that the first address is fixed $A$4, so think of that as an arbitrary reference point. As this formula is copied down the sheet, the $A$4 remains fixed (that's what the $ symbols do), but the A4 part of the formula will change to A5 on the next row, A6 below that, etc. ROWS returns the number of rows in this range, so values from this function will be 1 (A4 to A4), then 2 (A4 to A5), then 3 (A4 to A6), etc. There is nothing special about the specific cell reference chosen here. One could achieve the same thing by using ROWS($Z$1:Z1), or ROWS(A$57:$Z57). These may appear odd, but the important thing is the row designator that begins the range description must be fixed, and the second row designator in that range is the same number, but not fixed. The actual numbers used and the columns used are completely arbitrary. Generally it is preferred to use some ranges that apply more directly to the data. With that said, my formulas would be a little cleaner if I had used ROWS($A$4:$A4) so that the second "A" is fixed, and then when the formula is copied to other columns, the last two-thirds of all formulas would be identical.
With this formula on row 4 in the #10 sheet, the SMALL function extracts the smallest list position index (meaning the value of "1"), and this 1st item happens to be in list position 1, which INDEX uses to return data from other columns corresponding to that list position. When the formula is pulled down to the next row, the next smallest array element is extracted (meaning a value of "3"), so the 2nd item happens to be in list position 3, which INDEX uses in a similar manner to return data from other columns corresponding to that list position. Some user intervention is necessary with this construction, as you need to ensure that the formula is pulled down far enough to extract all of the data.
This approach works well with the INDEX function, since the only part of the formula that needs to be changed is the first argument of INDEX so that data from the desired column on the 'Raw Data' sheet is being extracted. Finally, it is likely that we'll attempt some things that technically produce errors...for example, trying to return the "3"rd smallest item in the list position array when there are only two non-empty elements. To prevent the formula from returning an error code, the entire function is wrapped inside an IFERROR function with the instruction to return a "" when an error is encountered.
Please post back if you run into any surprises or have other questions.
