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.
Answers
-
@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
-
Thnks Dale, I'll try this out and see if it works!!
Thanks
graham.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 305 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!