How do I create a column formula to pull values from a particular row in other sheets?

I've been playing with MATCH and VLOOKUP formulas, but I can't figure it out.

My target sheet contains (among other things) 3 columns:

  • The SHEET NAMES of the sheets that I want to pull from
  • START DATE (blank, want to auto-fill) for a particular task within the source sheet
  • FINISH DATE (blank, want to auto-fill) for a particular task within the source sheet

For example:

  • Sheet1Name | Start Date | Finish Date
  • Sheet2Name | Start Date | Finish Date

How do I make column formulas for these date columns to reference the sheet that is named, search for the task name within that source sheet, and auto-fill the start and finish date for that task?

The task has the same name in all the source sheets.

Thank you!


  • James Keuning
    James Keuning ✭✭✭✭✭

    You want to use MATCH and INDEX. The MATCH has to work first. Can you get the MATCH to work? You will know it is working because it will return a row number. Once that is working you use INDEX to grab the Start from that row number and the Finish from that row.

  • malden
    malden ✭✭
    edited 11/03/23

    @James Keuning Thanks for your response.

    EDIT: MATCH is working, but INDEX still isn't working. Within a source sheet (just for testing), I have:

    =INDEX([Start Date]:[Start Date], MATCH("Task A", [Task Column]:[Task Column], 0))


    The formula works for other columns, but not [Start Date] or [Finish Date].

    And, once I can get this INDEX formula working, I still need to know how to modify it as a column formula on the target sheet so that it will look up these values from sheets in the [Sheet Name] column there - rather than one sheet individually at a time.

  • malden
    malden ✭✭
    edited 11/03/23

    Nevermind. I found a different workaround.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!