IF THEN using multiple sheets and multiple columns

I am looking for a formula that brings over data from a column only if there is a date in another column.

=IFERROR({Dallas Tracker NEW Start Date} > 1, (IFERROR(INDEX({Dallas Tracker NEW Mileage}, MATCH([SEGMENT ID]525, {Dallas Tracker SEGMENT ID}, 0)), "")))

So basically if there is a start date in a specific column, I want to know what the mileage is in a different column. My problem with this formula is that it pulls over mileage from other columns that share the same SEGMENT ID if that data was listed first.

I know the first part of my formula has to be wrong. I just don't know where to begin with this one.

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    You would use an INDEX/MATCH on the date column to look at the cell that should contain the date.

    INDEX({Dallas Tracker NEW Date}, MATCH([SEGMENT ID]525, {Dallas Tracker SEGMENT ID}, 0))


    Then you would drop that into an ISDATE function as the criteria for an IF statement that says IF that IS a DATE, run this other INDEX/MATCH.

    =IF(ISDATE(INDEX({Dallas Tracker NEW Date}, MATCH([SEGMENT ID]525, {Dallas Tracker SEGMENT ID}, 0))), IFERROR(INDEX({Dallas Tracker NEW Mileage}, MATCH([SEGMENT ID]525, {Dallas Tracker SEGMENT ID}, 0)), ""))

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!