IF, Index, Match formula



I am trying to see if there is a formula that will look at the Cells in row 1, and if matched to the any of the weeks in column Week Of, will return the corresponding deliverable value in Column1. Attached is the clip of my smartsheet. Does anyone know if there is a formula that can do this? I have tried using IF, INDEX, MATCH, and have had no luck.

Thank you,




  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭

    Hi @Crystal Panning

    Hope you are fine, please try the following formula ( note for each column you need to put the column name as a criteria).

    =IFERROR(JOIN(COLLECT([Column1]:[Column1], [Week Of]:[Week Of], @cell = "Aug Wk1")), "")

    The following screenshot shows the result:

    PMP Certified


    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

  • Crystal Panning
    Crystal Panning ✭✭✭✭

    Thank you for providing this formula to try. I think we are close, but when entering the formula in the cell, it shows up as blank. There is no error message, just the cell blank. It looked like it worked for you, so not sure why the cell shows blank for me. Any suggestions?

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!