From another sheet, how can i lookup / pull a list of values ?

Eric Sivacis

Hello world,

I have two data sheets that both have columns that contain a identifier for what the row references.

I want to make a list in my third sheet that contains all the identifier values of these two data sheets that meet my criteria of containing letters xx in the end of their identifier names.

Is this possible? with match and index i can only get a single value, with lookup i cant get the identifier value as i need a id value to make the connection in the first place and automations dont work since they have alot of different columns in the sheets.

What should i use? Am i using the right formulas or any tips? Im looking at getting all values in a their own row.

SoS | Dan Palenchar

Hello @Eric Sivacis,

Use INDEX(COLLECT())!

=INDEX(COLLECT( {Column you want to pull from}, {Identifier Column 1}, [identifier to check against]@row, {Criteria Column 2}, [Identifier to check against]@row))

Eric Sivacis

Thank you

Eric Sivacis

https://community.smartsheet.com/discussion/comment/445015#Comment_445015

Hello, @Dan Palenchar

Identifier column 1 in the other sheet? or what is the differance from {Column you want to pull from} and {Identifier Column 1} ?

And what is the differance from [identifier to check against]@row and {Criteria Column 2}?

There is only one coloumn of identifiers i want to check if they fit the partial string containment criteria and then if they do, pull it into my other sheet

Thank you, this cleared up alot about the index function even tho i have more questions.

SoS | Dan Palenchar

Hey @Eric Sivacis,

I wrote out some answers to your specific questions at the end of this but from your last reply I think I misunderstood your use case.

If you want to search for a string and return a value where it contains that string you can use something like:

INDEX(COLLECT({Column you want to pull from}, {Identifier Column}, CONTAINS([Identifier column]@row, @cell))

More info:

• {Column you want to pull from} is the column that has the values you want to return. In other words, this is the column that contains the value you want to appear as the output of your formula.
• {Identifier Column #} is a column that you want to check for a value in and then pull the corresponding value out from {Column you want to pull from}
• [Identifier to check against] is one of the options in {Column you want to pull from} that will determine which specific cell in {Column you want to pull from}

For example, let's say you sell fruit and have a sheet with columns:

1. Fruit - list of fruits
2. In stock - checkbox for if it is available
3. Price - how much it costs

And you want to return the Price for a specific Fruit if it is checked In Stock

In this case:

• Price is the {Column you want to pull from}
• Fruit and In Stock are {Identifier Column 1} and {Identifier Column 2}
• Any specific fruit (i.e., Apple) would be [Identifier 1 to check against] and Checked/Unchecked (technically 1/0 for in stock/not in stock) would be [Identifier 2 to check against]

Eric Sivacis

https://community.smartsheet.com/discussion/comment/445198#Comment_445198

Ive since realised that "Identifier" should be seen as a reserved word in the smartsheet syntax. And i apologize for the confusion.

In my case i meant that Identifier is the info i want to have in my new sheet, a list of all the identifiers or "fruits" in this case:

I know that the fruit data sheet contains alot of fruits in this example ALL of the fruits,, so i want to pull every fruit that has "apple" in the name i.e green apple, red apple, crab apple, pine apple (all fruits have their own row in data sheet)

Into my new sheet → To be used as identifiers for other indexes that ive solved.

So i have a blank sheet, and a data sheet with ALL the fruits in existance,

I want the function in the blank sheet from {data sheet fruits column} bring in all the fruits that contain "apple" string so that my new blank sheet gets a new row for value like this:
green apple
red apple
pineapple
crabapple
etc..apple

SoS | Dan Palenchar

@Eric Sivacis,

Ah ok, so you want ONE cell that pulls in all the various entries in the database that have "apple"

In that case I think something like this would work

INDEX(JOIN(COLLECT({Column you want to pull from}, {Identifier Column}, CONTAINS([Identifier column]@row, @cell)),CHAR10)

CHAR(10) can be inserted in a formula to create a line break. The idea is that COLLECT() will pull anything that has "apple" or whatever Identifier you specify. JOIN() than places the CHAR(10) delimiter between each option to break it into separate lines

Eric Sivacis

https://community.smartsheet.com/discussion/comment/445596#Comment_445596

I ended up doing this:

So i also had to create a extra excell attachment for anyone interested

https://www.youtube.com/watch?v=11d1jSCmRq0