Workiva Support Center

Your Workiva Sustainability Reporting workspace may include Data Collection and Factbook spreadsheets, which use an (INDEX(MATCH,MATCH)) formula to identify metrics in Sustainability Program and retrieve their details and values from their Metric data and Data output sheets. For example, to retrieve a metric's name from the Metric data sheet, the Performance tables section of the Factbook spreadsheet uses this (INDEX(MATCH,MATCH)) formula in the Metric name cell:

(INDEX('Metric Data'!$A:$AA,MATCH($A4,'Metric Data'!$B:$B,0),MATCH(C$3,'Metric Data'!$A$1:$O$1,0))

Tip: From the Formula details panel, you can view detailed information about a selected cell's formula, including other cells its references.

To help understand the (INDEX(MATCH,MATCH)) formula, break it down to its core functions.

Tip: You can use the (INDEX(MATCH,MATCH)) formula to customize the Data collection or Factbook spreadsheet as necessary for your reporting.

INDEX function

Essentially, the INDEX function — as INDEX(range,row number,column number) — returns a specific value, based on the location specified by the MATCH functions.

In the example of the Metric name column within the Factbook spreadsheet's performance tables, INDEX('Metric Data'!$A:$AA) specifies where to look for the value:

• 'Metric Data'!, for the Metric data sheet of the Factbook spreadsheet

  Tip: The exclamation point (!) indicates that the function references a different sheet in the Spreadsheet than the formula itself.

• $A:$AA, for the range within the sheet, from the first row $A to the final column $AA

  Tip: The dollar sign $ before the range's row and column numbers — $A:$AA — indicate an absolute reference, which means the cell coordinates remain the same, even if you copy and move the formula.

In short, INDEX('Metric Data'!$A:$AA) references any cell within the Metric data sheet. The MATCH functions within the INDEX function then specify the specific row and column of the value to return.

MATCH function

The MATCH function — as MATCH(lookup value,lookup array,match type) — then looks for a specific value within the range specified by the INDEX function and returns its relative row position.

In the Metric name example, MATCH($A4,'Metric Data'!$B:$B,0):

• $A4 specifies the lookup value — in this case, the metric ID — based on its cell coordinates within the performance table.

  Tip: To look up metric details or values from the Metric data sheet of the Factbook or Data collection spreadsheet, use metric ID or dimension; from the Data output sheet, use metric ID, dimension, year, or collection period.

• 'Metric Data'!$B:$B specifies the lookup array, or where to find the lookup value; 'Metric Data'! for the Metric data sheet of the Factbook spreadsheet, and $B:$B for the row and column within that sheet.
• 0 specifies the match type; in this case, an exact match.

  Tip: To use an approximate match, enter 1 for a value less than or equal to the lookup value, or -1 for a value greater than or equal to the lookup value.

In turn, the second MATCH function — MATCH(C$3,'Metric Data'!$A$1:$O$1,0) — likewise returns the relative column position of the value.

Tip: In the second MATCH function, C$3 locks the row position based on the result of the first MATCH function, while the column number can change.

Additional functions

For some cells, the (INDEX(MATCH,MATCH)) formula may include additional functions — such as TEXTJOIN or CONCATENATE — to create a text value based on multiple strings. To learn more about these and other Spreadsheet functions, view Supported formula functions and operators.