The INDEX and MATCH functions are flexible lookup formulas used in Dashpivot tables and calculations to search for values and return matching results. These formulas are commonly used as an alternative to VLOOKUP or HLOOKUP because they support vertical lookups, horizontal lookups, and two-way searches across rows and columns. INDEX and MATCH are commonly used for configurable matrices, dynamic references, and Risk Matrix calculations in SWMS templates.
Understanding the INDEX Function
The INDEX function returns a value from a selected range based on its row or column position.
Formula:
=INDEX(<Range of Results>,<Row Number>,<Column Number>)
Example:
=INDEX(Table1!$B$1:$B$5,3)
This example returns the value from the third row within the selected range.
Understanding the MATCH Function
The MATCH function searches a range for a value and returns its position within that range.
Formula:
=MATCH(<Reference cell>,<Range of cells being searched>,0)
Example:
=MATCH(A1,Table1!$A$1:$A$5,0)
If the value in A1 is found in the second row of the lookup range, MATCH returns 2.
The 0 forces an exact match.
How INDEX and MATCH Work Together
MATCH identifies the position of a value, while INDEX returns the value stored at that position.
Example:
=INDEX(Table1!$B$1:$B$5,MATCH(A1,Table1!$A$1:$A$5,0))
In this example:
MATCH searches for the value in
A1.MATCH returns the matching row position.
INDEX returns the corresponding value from the results range.
Prerequisites
Before you begin make sure you have the following:
Plan: All Plan Types
Permission: Dashpivot Contributor
Skills Required: familiarity with excel-based formulas
Device Type: Computer/Browser
Subscription: Dashpivot
Step-by-Step Instructions
Y-Axis Lookup (Vertical Search)
Use this configuration to search a column for a value and return a corresponding value from another column.
Formula:
=INDEX(<Range of Results>,MATCH(<Reference cell>,<Range of cells being searched>,0))
Example:
=INDEX(Table1!$B$1:$B$5,MATCH(A1,Table1!$A$1:$A$5,0))
Select the cell where you want the result to appear.
Enter the INDEX function with the range containing the results.
Insert the MATCH function inside the INDEX formula.
Reference the lookup cell containing the value to search.
Select the column range containing the lookup values.
Add
0as the MATCH type to return an exact match.Complete the formula and apply it.
X-Axis Lookup (Horizontal Search)
Use this configuration to search a row for a value and return a corresponding value from another row.
Formula:
=INDEX(<Range of Results>,MATCH(<Reference cell>,<Range of cells being searched>,0))
Example:
=INDEX(Table3!$A$2:$E$2,MATCH(A1,Table3!$A$1:$E$1,0))
Select the cell where you want the result to appear.
Enter the INDEX function with the row containing the results.
Insert the MATCH function inside the INDEX formula.
Reference the lookup cell containing the search value.
Select the row range containing the lookup values.
Add
0as the MATCH type for an exact match.Complete the formula and apply it.
Two-Way Lookup (X and Y-Axis)
Use this configuration to search across both rows and columns to return a value from a matrix or grid.
Formula:
=INDEX(<Range of Results>,MATCH(<Reference cell>,<Range of cells being searched>,0),MATCH(<Reference cell>,<Range of cells being searched>,0))
Example:
=INDEX(Table5!$B$2:$F$6,MATCH(A1,Table5!$A$2:$A$6,0),MATCH(B1,Table5!$B$1:$F$1,0))
Select the cell where you want the result to appear.
Enter the INDEX function with the full matrix range containing the results.
Add the first MATCH function to identify the correct row position.
Reference the lookup cell for the row search.
Select the column containing the row lookup values.
Add
0as the MATCH type for an exact match.Add the second MATCH function to identify the correct column position.
Reference the lookup cell for the column search.
Select the row containing the column lookup values.
Add
0as the MATCH type for an exact match.Complete the formula and apply it.
Notes
Dashpivot formulas have limitations and are not fully identical to Microsoft Excel formulas.
Some Excel functions or formula behaviours may not be supported in Dashpivot.
Cells used in INDEX and MATCH formulas should be configured using supported field types such as Formula, List, Text, Number, Date, or Time fields.
If part of the referenced range contains a Prefilled Text field, the formula may return a
#N/Aerror.If the reference table is a prefilled table, use the
$symbol to create fixed ranges.INDEX and MATCH formulas can be used as an alternative to VLOOKUP or HLOOKUP.
Two-way lookup formulas are commonly used for Risk Matrix configurations in SWMS templates.
FAQ
Why am I getting a #N/A error?
This usually occurs when:
The lookup value cannot be found in the lookup range.
The lookup value contains extra spaces, incorrect capitalization, or typographical errors.
A referenced value is configured as Prefilled text in Dashpivot.
Why is the formula returning the wrong result?
This can occur if the MATCH function does not include 0 as the match type. Without 0, MATCH assumes the lookup list is sorted and may return incorrect positions.
Why does the formula stop working when copied?
This usually occurs when cell references are not locked using $, causing ranges to shift when copied across cells.
Why is the two-way lookup returning incorrect values?
This can occur when the row MATCH and column MATCH references are reversed or configured against the wrong ranges.
Why am I seeing mismatched range errors?
This occurs when the INDEX range and MATCH lookup ranges are not aligned or are different sizes.