Match+Index is a combination of functions that locates an item on your report and then returns the result to the desired location. While this combination has many sophisticated applications, you can start with a simple model.

For example, if you're interested in reporting on the top categories of sales by store, and you're using a pivot on store.

Using the Large function from the math-based functions, you can identify the largest value from a column.

The Large function requires two arguments: 

• which column you're looking at. 
• which position of large you're looking for (is it the 1st largest, 2nd largest, and so on).

1. For the first argument, the calculation looks at the sales-line total.

   

2. For the second argument, the calculation looks at the row number. This way, the highest value will be on top, the second-highest value will be in the second row, the third-highest value will be in the third row, and so on.

   

3. So the first calculation looks like:

   large(${sale_lines.total_sales_no_tax},row())

4. If you've prepared the calculation correctly, a column ranking the values from highest to lowest displays.

   

But it will only display the highest value, not the highest category.

Next, report on which row each value is coming from. You can use the Match function from the table-functions.

1. The Match function needs two arguments: which value we're looking for and the column in which you're looking for it. Following our best practices, let's open up a new calculation and start building it with brackets.

   

2. Then, copy the first calculation, referencing what you're looking for.

   

3. And paste it into the first set of brackets.

   

4. Next, add the second argument, where you're looking for it: the sales totals.

   

5. Now the calculation looks like:

   match((large(${sale_lines.total_sales_no_tax},row())),(${sale_lines.total_sales_no_tax}))

6. Click Save.

   

   And if you've prepared it properly, a second column displays, showing which row the value can be found in.

7. In the first store, this is unremarkable because the default sorting is from highest to lowest.

   

8. But in the other stores, the referenced high categories don't line up with their respective rows.

   

9. Now, keeping up with best practices, save a working version of this report.

   

Now, on your report, you don't just want to know what row the value is on, you want to know what the actual category is. If you know which row the value is on, you can ask Analytics to find the category from that row.

From the table functions, you can use the Index function to do this. The Index function needs two arguments: which column you're looking at, and which row you're asking it to look in.

1. Create a new calculation, starting it with brackets.

   

2. Enter the function to look at the column of Categories.

   

3. Then, copy the calculation from the field above.

   

4. Paste it into your final calculation below.

   

5. So the final calculation should look like:

   index((${cl_category_tops.top_level_category}),(match((large(${sale_lines.total_sales_no_tax},row())), (${sale_lines.total_sales_no_tax}))))

6. Click Save.

   

   If it was prepared correctly, you'll get a final calculation showing the label of the category corresponding to the value amount.

7. Click Save As New.

   

Optionally, you can customize the appearance to make it more meaningful.

1. First, hide the Dimension Top Level Category from visualization. You'll be using the Calculation to show us the Category labels.

   

2. Next, hide the Sale Line Total from visualization, because you'll also be using the Calculation to display these numbers.

   

3. Then, you can remove Calculation 2, showing us which row the values are on. This is not necessary to display, and is implicit in the final calculation, so it doesn't need to be on the report anymore.

   

4. Our visualization is looking a bit better.

   

Let's also change the order of Calculations, so that the Category shows up first, and the Total shows up second.

1. The best way to do this is start a new calculation, and then copy+paste the first one.

   

2. Into the last.

   

3. Then remove the first calculation.

   

4. Click Save.

   

You'll have a meaningful report showing you the top levels of categories and totals by store.

This approach to match+index works best when no duplicate values are anticipated. Using it to illustrate the quantity of products sold in a small time frame may create unanticipated results if multiple products are sold in the same quantity. In this case, more calculation may be required.