Example
In Figure 1, the Real Statistics array formula =IndexedValues(A2:D10) inserted in range F2:J6 is used to convert the data on the left side of the figure to the format on the right side of the figure.
Figure 1 – Indexed values conversion
The second argument in the IndexedValues(R1, type) function takes the values 0 for sum (default), 1 for use last match, or 2 for use last match but if there are multiple matches then the value “dupe” is returned.
The formula =IndexedValues(A2:D10,1) returns the same output as shown in the figure above, except that cell H3 contains the value 30 (instead of 110, which is 80+30) and all the zero values are replaced by blanks. The formula =IndexedValues(A2:D10,2) returns the same output except that cell H3 now contains the value “dupe”.
Worksheet Functions
Real Statistics Functions: The Real Statistics Resource Pack provides the following array functions.
IndexedValues(R1, type): converts the data in R1 to the format described above based on the specified type where type = 0 for “sum” (default), type = 1 for “use last match” or type = 2 for “use last match” but if there are multiple matches then the value “dupe” is returned.
IndexedCounts(R1): converts the data in R1 to the format described above where the last column specifies the number of matches
IndexedCount Example
The formula =IndexedCount(A2:C10) returns the same output as shown in Figure 1 except that all the non-zero values are replaced by 1 except for cell H3, which contains the value 2 (since there are two matches). Note that column D is not used.
Examples Workbook
Click here to download the Excel workbook with the examples described on this webpage.