Formula combinations for cross sheet references

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

• Review the comprehensiveFunction List.
• Download theFormula Template.
• Ask about your specific use case in theSmartsheet online Community.

Cross-sheet formula solutions

The following formulas combine different functions and work well for cross sheet data gathering. Learn more aboutreferencing 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. 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:

=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 theFormula Handbook templateto 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