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

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,

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