Combine values in column only if value in each row is less than a value

I have a column with an identifier in each row and in another column I have a value in each row. I want to search the second row for any values that are less than a reference cell, and if less that that reference cell, I want the text from the first column to be displayed in the cell the formula is in separated by , if multiples.

Column 1 | Column 2

ABC | 2

ABD | 2

AAD | 3

For example if the reference value were 3, I would want the cell with the formula to return "ABC, ABD".

Any help appreciated!


  • something like this ? play around with it abit

    if you make an extra column and use the same row to display the data, you can use

    =IFERROR(JOIN(COLLECT(Column1:Column1; Column2:Column2; Column2@row < Column2@row)); "")

    mind im using ; you might need ,

    =IFERROR(JOIN(COLLECT(Column1:Column1, Column2:Column2, Column2@row < Column2@row)),"")

    if u use an independent field where you put in the amount for example 4 , then show data of everything below 4 in a join collect then refere to that field instead

    Then you can put this code wherever you want

    =JOIN(COLLECT(Column1:Column1, Column2:Column2, Column2@row < {independent field}))

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!