If and Match Formula Help

Melissa Larkin
Melissa Larkin ✭✭✭
edited 08/21/23 in Formulas and Functions

Hello - I have 2 sheets. One that shows all the fields from our on-prem environment and another that shows all the fields that were migrated to our sandbox via a migration tool. I want to know if any fields were not migrated from our on-prem environment to the sandbox. Any suggestions on an equation to use? I tried the following question and it worked for the first few rows but then I got an invalid data type: =IF(MATCH(Helper@row, {Metadata - Sandbox - Test - 2023_08_18 Range 1}), "Migrated to Sandbox"). I'm not sure what I'm doing wrong :(

Best Answer

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓

    Hi @Melissa Larkin

    What I would do in this instance is actually use a COUNTIFS formula to "count" the number of rows in your second sheet that have the same data as in your current sheet.

    The COUNT will be 0 if it hasn't been migrated, or 1 if it has.

    Try this:

    =IF(COUNTIFS({Metadata - Sandbox - Test - 2023_08_18 Range 1}, Helper@row) > 0, "Migrated to Sandbox", "Not Migrated")




Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!