I am trying to use contains to return another cell.

Options

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.

Answers

  • Dale Murphy
    Dale Murphy ✭✭✭✭✭✭
    Options

    @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.

    dm

  • GrahamWelsby
    Options

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

    Thanks

    graham.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!