Index Match, how to recognize multiple values?
Hello,
I have an issue with my index match formula. I want to locate the facility for a serial number. This works perfectly when there is only one value inside. However to reduce the amount of columns and options on the corresponding form, I changed it to one column that has multiple values. The formula is having difficulty seeing that the serial number exists in that column because there is two in the cell. How do i fix this?
Working formula below, ( when it returns to stock it goes positive, signifying that it is back at the home warehouse named "pes")
=IF(Available@row > 0, "PES", INDEX(Facility:Facility, MATCH(6633112443, [Microscope: 1]:[Microscope: 1], 0)))
When i change the column to allow multiple values in one cell however, it no longer recognizes that serial number is there.
Any help is appreciated!
Thanks,
Best Answer
-
Try this
=IF(Available@row > 0, "PES", JOIN(INDEX(COLLECT(Facility:Facility, [Microscope: 1]:[Microscope: 1], HAS(@cell,"6633112443")), 1)))
Will this work for you?
Kelly
Answers
-
Try this
=IF(Available@row > 0, "PES", JOIN(INDEX(COLLECT(Facility:Facility, [Microscope: 1]:[Microscope: 1], HAS(@cell,"6633112443")), 1)))
Will this work for you?
Kelly
-
I am not sure how that works, but it seems to work perfect!! I can finally progress the sheet and form. This has been stumping me since Wednesday. I am newer to smartsheets and do not quite understand how the order or parameters should be filled when using more than one function at a time.
Amazing :) This will be a good week
Thanks
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.7K Get Help
- 405 Global Discussions
- 216 Industry Talk
- 456 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 297 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!