How to use Collect & Contains function to pull value from different sheet?

ABHI

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.

Leibel S

See below

=INDEX(COLLECT({Fire Extinguisher & Eye Wash Inspection Range 1}, {Fire Extinguisher & Eye Wash Inspection Range 2}, CONTAINS([Tracking No]@row, @cell)), 1)

Leibel S

@Hollie Green

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 + "")

Leibel S

use contains function with @cell for your criteria

CONTAINS("Word",@cell)

ABHI

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

Leibel S

See below

=INDEX(COLLECT({Fire Extinguisher & Eye Wash Inspection Range 1}, {Fire Extinguisher & Eye Wash Inspection Range 2}, CONTAINS([Tracking No]@row, @cell)), 1)

ABHI

Thanks @Leibel S It worked like a champs.

Hollie Green

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)), "")

Genevieve P.

Hi @Hollie Green

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

Hollie Green

It's a text/number column. If I take out the if error it returns a #INVALID VALUE

Genevieve P.

Hi @Hollie Green

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

Hollie Green

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

Genevieve P.

Hi @Hollie Green

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?

Hollie Green

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

Hollie Green

Note the Ticket ID column and the Range 1 is on separate smartsheets

Leibel S

@Hollie Green

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 + "")

Hollie Green

That fixed it! Thank you so much Leibel!

Genevieve P.

Thanks for jumping in, @Leibel Shuchat! 🤩