Using Index, Collect and Contains in a cross sheet formula


I am working on a formula to look up a budget balance from a previous year's sheet and carry it forward based on two criteria, the {2023lineitemtype} and the {2023ExpenseDescription}. I am not sure where the formula is breaking. I could also be fundamentally misunderstanding how to use the contains function. I read some answers on these forums to help.

My understanding is that Criteria 1 of the Collect function would narrow the search down to any row that contains "Actual" in it. There is likely thousands of lines like that.

Criteria 2 would narrow that list down based on the exact text found in the {2023ExpenseDescription}.

=INDEX(COLLECT({2023BudgetBalance22}, {2023LineItemType}, CONTAINS("Actual ", @cell), {2023ExpenseDescription}, [Funding Source Description]@row), 1)

The source data might look like below:

Sheet 2023

Budget Balance LineItemType Expense Description

$5000 Project1 S&B Project 1 Actual S&B

$6000 Project1 Travel Project 1 Planned Travel

$2300 Project1 Travel Project1 Actual Travel

The other sheet, Sheet 2024, would use a column with "Project1 Travel" as a value to help pull the right data.

In this case I am interested in Project 1 Actual Travel Expenses.




  • KPH
    KPH ✭✭✭✭✭✭

    Hi @Ryan Williams

    It looks like you almost have it, but you have your column names reversed. Looking at that snippet of data I think you meant to do this:

    =INDEX(COLLECT({2023BudgetBalance22},  {2023ExpenseDescription}, CONTAINS("Actual ", @cell), {2023LineItemType}, [Funding Source Description]@row), 1)

    Your Expense Description is the column with Actual in, and LineItemType is the column that will match with Funding Source Description.

  • Ryan Williams

    thanks @KPH

    When I opened up my sheet this morning, the formula is now working. I started to implement in throughout the sheet and i am now getting an "#INVALID VALUE" error. I checked my column types and they are both the same, Text/Number. This was the same error I was getting yesterday. It seems odd that it just takes overnight for it to start working.

    Could there be something causing the invalid value that I am not considering?

  • KPH
    KPH ✭✭✭✭✭✭

    The Invalid value error is probably because there is not a match and therefore nothing to collect and index. If you can't spot anything, can you share a screen shot of your data?

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!