Matching Comma-Separated Values in a Cell Using Smartsheet Formulas

Bobby1

Hello Smartsheet community,

I have a consolidated tracking sheet that captures details like vendor names, contract numbers, invoice numbers, warrant numbers, and vendor numbers. In my current setup, if a vendor has multiple contract numbers, they are all contained within a single cell separated by commas. This pattern holds true for columns with multiple invoice numbers, warrant numbers, and so forth.

My question is about Smartsheet's formula capabilities: Can Smartsheet differentiate between individual contract numbers in a cell when they're separated by commas? More specifically, if I need to check for a match of a specific contract number in another Smartsheet, can the formula identify a match within a cell containing comma-separated values, or would each contract number need to be in its own individual cell for this type of validation?

Ryan Kramer

I haven’t seen anything like this yet in single formula.

Basically you would need to create a dynamic lookup split by commas and have each one of those perform the lookup and then check if any came back true.

Though difficult using a formula, very easy with some simple coding where you export the data, split it in the same way and then iterate through your lookup column for matches and on march do the next step.