Need help with calculation formulas - prepping for a dashboard

I'm trying to pull certain data from a test reference data sheet into a calculation sheet to later summarize for a dashboard.

Here is my test reference data sheet

I want to ask the question for the second column (also called "Range 1) IF the text "Jan 2023" appears in one of the cells in this column to return the "Title" in the third column (also called "Range 3").

I've tried different iterations and keep getting #unparsable. Appreciate help on how to reference a column range in another sheet and return values using the drag down feature for each row in that column.

Thank you!



  • Please try this formula in the third column ..this will match each value with "jan 2023" with "title A" otherwise this will give you a "no match" value.

    =IF([Test reference Sheet Range 1]@row = "jan 2023", "title A", "no match")

    This formula will apply to jan 2024 with the title B:

    =IF([Test reference Sheet Range 1]@row = "jan 2023", "title A", IF([Test reference Sheet Range 1]@row = "jan 2024", "title B", "no match"))

    you can add an if statement for the rest of the entries and so on...

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!