I am trying to use contains to return another cell.


I am using the contains formula to find a row - the cell has multiple unique numbers associated with a customer, I can return 'yes' if it's true and find this, but I need to return another cell on the same row.

I can't figure this out, as index match don't work unless there is a single unique number.


  • Dale Murphy
    Dale Murphy ✭✭✭✭✭✭

    @graham18628 I feel like I am getting two comments for one here because I just showed this example elsewhere. But I think this is what you need:

    =IFERROR(INDEX(DISTINCT(COLLECT({LOMDelivery Range 1}, {LOMDelivery Range 2}, <>"")), Check@row), "") - this formula is retrieving a value to place in [SONumber] for use later (below).

    • collect picks out the rows that meet a certain condition
    • Distinct refines the selection set and makes sure you only get one of any value
    • Index puts the item onto your sheet
    • once I have that unique value I use match (in a different column) to retrieve a value from a different column:
    • =INDEX({LOMDelivery Range 3}, (MATCH([SONumber]@row, {LOMDelivery Range 1}, 0)), 2)

    See if that can work.


  • GrahamWelsby

    Thnks Dale, I'll try this out and see if it works!!



Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!