array indexing?

ethanqt

I need a way to list out items from one spreadsheet to another if a certain condition is met. For example, I have an array formula that I used in excel that would look at an option selected from a drop-down menu and from that selected value, index/match a whole array of data that related to the option.

Brian_Richardson

You want to use INDEX/MATCH or INDEX/COLLECT for this.

See this article for the formatting and examples:

https://www.smartsheet.com/content-center/best-practices/tips-tricks/3-formulas-look-up-data-smartsheet

Brian_Richardson

Then you will need to work the formula with something like a row number as shown above to get each result from the collect in turn for each row. Smartsheet looks like a spreadsheet but operates like a database, so each row is a standalone record. Meaning there’s no native functionality to populate multiple rows from the results of one formula.

Brian_Richardson

You want to use INDEX/MATCH or INDEX/COLLECT for this.

See this article for the formatting and examples:

https://www.smartsheet.com/content-center/best-practices/tips-tricks/3-formulas-look-up-data-smartsheet

brianschmidt

Yep, what he said^^^. One other to consider is VLOOKUP. However, I tend to default to INDEX(COLLECT( where possible.

https://help.smartsheet.com/function/index

https://help.smartsheet.com/function/vlookup

ethanqt

https://community.smartsheet.com/discussion/comment/441173#Comment_441173

I did try INDEX/MATCH but what happened is that when I selected an option from my drop down menu (Option 1), the INDEX/MATCH would only pull one data point from my data source sheet when I need it to pull a whole column. In Excel I would be able to do it with an array function. So basically what I am trying to accomplish is when I select an option from my drop down menu, it will look into my data source tab and pull all the data that relates to that option.

This was the function I used for the Time, Over Freq. and Under Freq. column in my Main Sheet.

=INDEX({Time}, MATCH($[Region Selection]$1, {Identifier}, 0))

=INDEX({Over Freq.}, MATCH($[Region Selection]$1, {Identifier}, 0))

=INDEX({Under Freq.}, MATCH($[Region Selection]$1, {Identifier}, 0))

Brian_Richardson

Are you trying to post a column of data from the second sheet, into a cell on the first sheet? You can do that with a JOIN(COLLECT formula. INDEX/MATCH will always give you a single first match. INDEX/COLLECT will also give you a single return, selected from an array of matches. JOIN/COLLECT will give you the entire array in text, delimited how you choose.

= INDEX ( COLLECT ( {column array you want} , {criteria range1}, criteria1, {criteria range2}, criteria2), 1) will return the first value in the results array.

The {} references in the formulas indicate a cross-sheet reference, which you get by clicking "Reference Cell in Another Sheet" link in the formula popup helper as you type it out.

=JOIN ( COLLECT ( {column array you want} , {criteria range1}, criteria1, {criteria range2}, criteria2), ";" ) will post the data into a single cell, semicolon delimited. Or you can use CHAR(10) instead of ";" which will give you the results in a "wrapped" column in "rows" within the results cell.

There is no formula that will populate down multiple rows from a single formula. You need to write your formulas in a way to pull the data you want onto a single row. If you need multiple rows of results then you'll need to get fancier with a JOIN/COLLECT statement to return each array element on a separate row by referencing a row number or other incremental number on each row.

= INDEX ( COLLECT ( {column array you want} , {criteria range1}, criteria1, {criteria range2}, criteria2), [Row Number]@row) will return the value in the position of your results array that corresponds to your row number. You can get row numbers by setting up an autonumber column, then a second "Row Number" column with this formula =MATCH(Auto:Auto, Auto@row)

ethanqt

My goal is to have a drop down menu where when an option is selected, It will pull all the data from the Time column from my data source sheet and put it in my main sheet but not all in one cell. For example, If I select EAST from my drop down menu I want the time column in my Main sheet to have the column of values from my Time column in the data source sheet but I want it to NOT all be in one cell.

Brian_Richardson

Then you will need to work the formula with something like a row number as shown above to get each result from the collect in turn for each row. Smartsheet looks like a spreadsheet but operates like a database, so each row is a standalone record. Meaning there’s no native functionality to populate multiple rows from the results of one formula.