How does Collect function apply in this case?

User251

For the sake of data integrity in a big sheet that has a column for a cost name and its amount, I need the same values to be populated for the same cost name. for instance: if one column has items names and the second has values:

Item1 20

Item2 30

Item3 40

Item 1 (I need to have here 20 in this cell)

Item3 (I need to have 30 in this cell)

I read about the combination of Collect and Index in one answer but I am not sure how it can work in my case?

Genevieve P.

Hi @User251

Are the cells you want to fill in the same sheet and column or in a different sheet?

If you're looking to populate this same sheet with the values, what I would do is have a second sheet that simple has a Table of all the items and their associated costs. Then you can use the INDEX(MATCH formula to search the reference table and return the matching cost value! Does that make sense?

An INDEX(MATCH works like this:

=INDEX({Column with value to return}, MATCH([Value to match]@row, {Column with value to match}, 0))

So in your case, if you built a table sheet that only had two columns:

=INDEX({Cost Column reference sheet}, MATCH([Item Column]@row, {Item Column reference sheet}, 0))

If you only have a small number of Items, another option could be to use the Change Cell workflow so that based on what Item is listed, a set value populates in the Cost column.

Let me know if I can clarify anything further for you and I'd be happy to help!

Cheers,

Genevieve

Genevieve P.

Hi @User251

Are the cells you want to fill in the same sheet and column or in a different sheet?

If you're looking to populate this same sheet with the values, what I would do is have a second sheet that simple has a Table of all the items and their associated costs. Then you can use the INDEX(MATCH formula to search the reference table and return the matching cost value! Does that make sense?

An INDEX(MATCH works like this:

=INDEX({Column with value to return}, MATCH([Value to match]@row, {Column with value to match}, 0))

So in your case, if you built a table sheet that only had two columns:

=INDEX({Cost Column reference sheet}, MATCH([Item Column]@row, {Item Column reference sheet}, 0))

If you only have a small number of Items, another option could be to use the Change Cell workflow so that based on what Item is listed, a set value populates in the Cost column.

Let me know if I can clarify anything further for you and I'd be happy to help!

Cheers,

Genevieve

User251

https://community.smartsheet.com/discussion/88756/how-does-collect-function-apply-in-this-case

Thank you so much @Genevieve P. That was very helpful!