Formulas Equivalent of an array?
Hello! I am trying to write a formula that pulls information from one sheet into another based on a criteria. I have successfully written the formula to work for a single row, but can not get it to repeat for successive rows. I believe I'm looking for Smartsheet's equivalent to an array. I understand that a cell range is somewhat equivalent, and I believe the solution is in fixing how Smartsheet is creating a reference range, but I don't know how to fix it.
Here's my formula that's functional for one row:
=IF(NETWORKDAYS(TODAY(), {CZI Project Plan Range 2}) < 14, {CZI Project Plan Range 3}, 0)
CZI Project Plan Range 2 = a target end date column in another sheet, reference spans the whole column
CZI Project Plan Range 3 = what I want to feed into the cell. In the working formula, it's a single cell. I want it to be an entire column, but when I change the range to an entire column, the formula breaks.
Potentially helpful context 1 I'm comparing a project task target end date to today's date, and I want the sheet to populate with the task name if it's target date is within 14 working days.
Potentially helpful context 2 I'm doing it this way because I want this to populate a project agenda sheet. I realize I can easily get this information from filtering the project sheet and/or creating a report. But I can't feed that information into another sheet that I can functionally work with, so I'm trying this formula approach.
Thanks for any assistance!
Answers

Hi @April Urban
Crosssheet formulas can only populate one cell, so you won't be able to bring back a filtered column list into another column, but we could bring back all the matching column values into one cell if that would work for you.
The way to do this is with the COLLECT function. COLLECT essentially filters down your results and gives you the column values based on your criteria. You can either use INDEX(COLLECT to find the first matching value, or JOIN(COLLECT to bring back ALL matches.
A JOIN(COLLECT works like this:
=JOIN(COLLECT({Column to Return}, {Criteria Column 1}, "1st Criteria", {Criteria Column 2}, "2nd Criteria"), ", ")
The ", " at the end is what will separate your multiple values.
In your case, try something like this:
=JOIN(COLLECT({CZI Project Plan Range 3}, {CZI Project Plan Range 2}, NETWORKDAYS(TODAY(), @cell) < 14), " / ")
Once again, this will populate all matches into one cell so you'll have one long string of task names. The other way to do this would be to have a unique ID that would help the formula find matches across sheets  then we could add this as the second column and criteria.
Cheers!
Genevieve

Thanks! I think the index function might work better for this purpose. It seems like then I could populate some number of cells with this formula and maybe make a note that the list may be truncated if greater than the number of rows populated with the cell. I'll look into how to use the index function, and feel free to send more instructions and tips!

There is a work around regarding multiple criteria's in an array search  Tabulate the array and assign a value to each outcome  sumifs the value with the text or parameter as criteria's then index match that value to display the desired array outcome. This will remove the requirement to have nested if statements.
Example Code
=IFERROR(INDEX({Risk Rating Outcome}, MATCH((SUMIFS({Outcome}, {Array Search}, [Inherent Likelihood]@row, {Array Search 2}, [Inherent Consequence]@row)), {Outcome}, 0)), "Complete Inherent Consequence/Likelihood to define Inherent Risk Rating")
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 10.6K Get Help
 63 Global Discussions
 46 Industry Talk
 387 Announcements
 3.5K Ideas & Feature Requests
 55 Brandfolder
 125 Just for fun
 50 Community Job Board
 466 Show & Tell
 40 Member Spotlight
 44 Power Your Process
 28 Sponsor X
 234 Events
 7.3K Forum Archives
Check out the Formula Handbook template!