Formula combinations for cross sheet references

Applies to

Smartsheet
  • Pro
  • Business
  • Enterprise

Capabilities

Who can use this capability

The Owner, Admins, and Editors can add or edit references. Viewer access or higher is required for the sheet being referenced.

There are countless ways to combine functions to analyze your data and make it more useful. This article includes some frequently used functions and additional resources to help you make the most out of formulas. 

Cross-sheet formula solutions

The following formulas combine different functions and work well for cross sheet data gathering. For information on creating cross-sheet references, see Formulas: Reference Data from Other Sheets

Any of the criteria referenced in quotes can be substituted with a direct cell reference, such as [Column Name]@row, where the value is located in a cell instead of typed into the formula. See: Create a Cell or Column Reference in a Formula

Lookup one cell using a matching value

Bring back a value from one column based on a matching value in a different column to identify the row. Use this instead of a VLOOKUP for better sheet performance, reducing cross-sheet cell references. 

Use INDEX and MATCH:

=INDEX({Column to return}, MATCH("Matching Value", {Column with value to match}, 0))

The "Matching Value" will need to be unique across both sheets and stored in the same type of column. For example, if you put a filter on your sheet with that value then only one row should appear. If you have multiple rows with the "Matching Value", the formula will only return the first match it finds. See the two solutions below for possible alternatives in this scenario. 

Lookup one cell using multiple criteria

Bring back a value from one column based on multiple matching values in other columns. 

Use INDEX and COLLECT:

=INDEX(COLLECT({Column to return}, {Column 1 with value to match}, "Value 1", {Column 2 with value to match}, "Value 2"), 1)

Gather all matching content into one cell

Bring back all cells that match your criteria into one cell. This is similar to using INDEX but it allows you to view all possible matching values if you have more than one row in the source sheet with matching data. 

Use JOIN and COLLECT:

=JOIN(COLLECT({Column with values to return}, {Criteria Column 1}, "Criteria 1", {Criteria Column 2}, "Criteria 2"), ", ")

Average with multiple conditions

Average one column based on multiple criteria in other columns; essentially a plural AVERAGEIF, allowing you to add multiple filter conditions to determine what rows to average. 

Use AVG and COLLECT:

=AVG(COLLECT({Column to Avg}, {Criteria Column 1}, "Criteria 1", {Criteria Column 2}, "Criteria 2"))

Lookup latest date or highest number with conditions

Bring back the latest date or highest number from one column based on multiple criteria in other columns. 

Use MAX and COLLECT:

=MAX(COLLECT({Column to find Max Date or Number}, {Criteria Column 1}, "Criteria 1", {Criteria Column 2}, "Criteria 2"), ", ")

You can use MIN instead of MAX if you are looking for the earliest date or lowest number. 

Still need help?

Use the Formula Handbook template to find more support resources, and view 100+ formulas, including a glossary of every function that you can practice working with in real time, and examples of commonly used and advanced formulas.

Find examples of how other Smartsheet customers use this function or ask about your specific use case in the Smartsheet online Community.

Ask the Community