Formula combinations for cross sheet references

This article includes frequently used functions and additional resources to help you make the most out of formulas. 

Who can use this?

Plans:

  • Pro
  • Business
  • Enterprise

Permissions:

  • Owner
  • Admin
  • Editor

Find out if this capability is included in Smartsheet Regions or Smartsheet Gov.

There are countless ways to combine functions to analyze your data and make it more useful.

Cross-sheet formulas

The following formulas combine different functions and work well for cross-sheet data gathering. Learn more about referencing data from other sheets.

Any 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. Learn how to 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 INDEX and MATCH, for example:

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

Use this instead of a VLOOKUP for better sheet performance, reducing cross-sheet cell references. 

  • The Matching Value must be unique in the source sheet and stored in the same column type across both sheets. For example, if you put a filter on the source sheet using that value, only one row should appear. If the value isn't unique in the source sheet, INDEX/MATCH only retrieves the value closest to the top of the sheet; later values get ignored. However, the lookup value in the destination sheet doesn't have to be unique, and you can retrieve it multiple times when using INDEX/MATCH.
  • If you have multiple rows with the Matching Value, the formula returns the first match it finds. 

See the following solutions for 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, for example:

=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 you can view all possible matching values if you have more than one row in the source sheet with matching data. 

Use JOIN and COLLECT, for example:

=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, for example:

=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, for example:

=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. 

Optimize cross-sheet references

The total number of cells that formulas can reference in a sheet can reach 25 million. If you exceed this limit, you're prompted to reduce the number of cells referenced by formulas.

To optimize the number of cells referenced by formulas in your sheet, here are a few helpful tips:

  • Use INDEX(MATCH) instead of VLOOKUP: VLOOKUP references an entire table range, while INDEX(MATCH) only refers to the necessary columns, reducing the number of cells involved.
  • Distribute formulas across multiple sheets: Instead of using a single master sheet, spread your data by creating separate sheets for different request types. This helps to distribute formulas more efficiently.
  • Reference only essential cells: Formulas should only refer to the specific cells needed for calculations, which can help reduce the total number of referenced cells.

These steps can help you make the most of the software's capabilities and improve performance.

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