How to use Collect & Contains function to pull value from different sheet?
I cannot use Index(Collect) because i am comparing the strings as criteria and it doesn't have to be exactly same, even if a word is match, I want my formula to execute.
Best Answers
-
See below
=INDEX(COLLECT({Fire Extinguisher & Eye Wash Inspection Range 1}, {Fire Extinguisher & Eye Wash Inspection Range 2}, CONTAINS([Tracking No]@row, @cell)), 1)
-
Seems like the contains function foes not work well with numbers...
Try changing the contains function
From: CONTAINS([Ticket ID]@row, @cell)
To: CONTAINS([Ticket ID]@row, @cell + "")
Answers
-
use contains function with @cell for your criteria
CONTAINS("Word",@cell)
-
=INDEX(COLLECT({Fire Extinguisher & Eye Wash Inspection Range 1}, {Fire Extinguisher & Eye Wash Inspection Range 2}, [Tracking No]@row), 1)
I have used above formula which works fine for the cell that has exact match but there are some cells that doesnot have exact match. I am trying to see if I can use collect and contains .
-
See below
=INDEX(COLLECT({Fire Extinguisher & Eye Wash Inspection Range 1}, {Fire Extinguisher & Eye Wash Inspection Range 2}, CONTAINS([Tracking No]@row, @cell)), 1)
-
Thanks @Leibel S It worked like a champs.
-
I am trying to do a similar formula but when I add in the Contains it gives an error and all of my cells that would not return Not Percepta are blank.
This is the formula that gives an error =IFERROR(IF(Oracle@row = "Not Percepta", "Not Percepta", INDEX(COLLECT({Xirgo Shipping Approval Tracker Range 2}, {Xirgo Shipping Approval Tracker Range 1}, CONTAINS([Ticket ID]@row, @cell)), 1)), "")
This formula works but doesn't have the contains so a couple of cells are blank that I don't need to be because it isn't finding the result of the cell containing that information =IFERROR(IF(Oracle@row = "Not Percepta", "Not Percepta", INDEX(COLLECT({Xirgo Shipping Approval Tracker Range 2}, {Xirgo Shipping Approval Tracker Range 1}, [Ticket ID]@row), 1)), "")
-
What is the error you're seeing?
Can you also confirm what type of column you're looking at in the {Xirgo Shipping Approval Tracker Range 1} range (text/number, multi-select, etc).
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
It's a text/number column. If I take out the if error it returns a #INVALID VALUE
-
Thanks for confirming! Your formula structure is correct, so this indicates that there's something going on with that range and criteria that's blocking it from working properly.
Can you try using a COUNTIF formula to see if you get the same error:
=COUNTIF({Xirgo Shipping Approval Tracker Range 1}, CONTAINS([Ticket ID]@row, @cell))
If this is giving you an error, we'll want to check the value you have in the [Ticket ID]@row cell versus what's in your {Range}.
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
The countif doesn't give an error message but it doesn't count the ones that are an exact match only the ones that have additional numbers included
-
Can you post a screen capture of both your [Ticket ID] column and the {Range 1} you're looking for the ticket IDs in? If not the actual data, example data?
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
This is example data. It will count the ones that say 12349 & 23941 and it will count the one that says Ticket 16542 but will not count the ones that say 12345 or 13245
-
Note the Ticket ID column and the Range 1 is on separate smartsheets
-
Seems like the contains function foes not work well with numbers...
Try changing the contains function
From: CONTAINS([Ticket ID]@row, @cell)
To: CONTAINS([Ticket ID]@row, @cell + "")
-
That fixed it! Thank you so much Leibel!
-
Thanks for jumping in, @Leibel Shuchat! 🤩
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 145 Just for fun
- 62 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!