I am trying to use contains to return another cell.

GrahamWelsby

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

@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

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

Thanks

graham.