Formula combinations for cross sheet references

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

• Review the comprehensive Function List. 
• Download the Formula Template.
• Ask about your specific use case in the Smartsheet online Community.

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 this instead of a VLOOKUP for better sheet performance, reducing cross-sheet cell references. 

Use INDEX and MATCH, for example:

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

The Matching Value must be unique across both sheets and stored in the same column type. For example, only one row should appear if you put a filter on your sheet with that value. If you have multiple rows with the Matching Value, the formula returns 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, 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 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, 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 will be 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