Using Index, Collect and Contains in a cross sheet formula

Ryan Williams

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.

Thanks!

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

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?