Assistance with Cross-Sheet Formula for Matching Keywords in Smartsheet

whitehouse

Hi Smartsheet Community,

I'm trying to create a formula in Smartsheet to match specific keywords in a column from a referenced sheet and return corresponding Risk IDs. However, I keep encountering the #INVALID REF and #UNPARSEABLE errors, and I need help understanding what might be wrong.

Here’s what I’m attempting to do:

1. Scenario:
   • I have a "BRD" sheet with a Risk ID column.
   • I have a "Risk Register" sheet with a Keywords column containing keyword phrases (e.g., "break time," "compliance").
   • I want to check if any of the keywords from the "Risk Register" sheet match data in the "BRD" sheet and return the corresponding Risk ID (e.g., "R001").
2. =IF(CONTAINS("break time", {Risk Register Keywords}), "R001", "")
   • {Risk Register Keywords} is a cross-sheet reference created via "Reference Another Sheet."
   • I’ve verified that the reference name {Risk Register Keywords} matches the correct column in the source sheet.
3. Error:
   • The formula returns #INVALID REF or #UNPARSEABLE.
4. Questions:
   • Is my formula structured correctly for this use case?
   • Do I need to reformat my referenced range or adjust my logic for multiple conditions?
   • Are there best practices for dynamically referencing keywords from a different sheet while using IF and CONTAINS in Smartsheet?

Thank you for your guidance! I’d appreciate any step-by-step instructions or insights.

Melissa Yamada

Hello @whitehouse

You might want to use the INDEX/MATCH functions instead.

Try this:

=INDEX({Risk ID},MATCH("Break Time",{Risk Register Keywords},0))

INDEX function will return the Risk ID

MATCH function will be the criteria to look at for which Risk ID matches the keyword.