If formula to return a date

Hi!

I need a formula to look at another sheet, if it matches criteria, return the date.

I have one sheet that lists all of the requests for W9. On my base sheet, I need to look at the W9 sheet and show the date the W9 was received. I have tried an IF formula, but continue to get Invalid Data Type. This is what I used: =IF({W9 Tracker Property}, "7341", {W9 Tracker Date Rec}).

I'm sure it's something simple, but I've tried reversing the order, using =, eliminating "", and etc.

Thanks in advance for any help!

Darla Brown

What you meditate on, you empower!

Overachiever - Core Product Certified - Mobilizer - EAP

Best Answer

  • Darla Brown
    Darla Brown Overachievers
    Answer ✓

    UPDATE (FIXED):

    I did go back and check the source data. In between the time I had initiated this discussion and began the work, someone had entered a duplicate entry. This did cause the blank cell. Once I deleted the duplicate, the date pulled in.

    To prepare for any future duplications with multiple properties using same agencies, I made a combo formula column: =[Property #]@row + [Agency Name (Must match W9)]@row. Then I used that column in my INDEX formula: =INDEX({W9 Tracker Date Rec}, MATCH("7308" + [Name of Agency]@row, {W9 Tracker combo}, 0)).

    This is working. Thanks for the help @Paul Newcome and @Vojtech Gajdos !

    Darla Brown

    What you meditate on, you empower!

    Overachiever - Core Product Certified - Mobilizer - EAP

Answers

  • Hi @Darla Brown

    Try this:

    =INDEX({W9 Tracker Date Rec}, MATCH("7341", {W9 Tracker Property}, 0))

    It should do the job you are after...

    Regards

    Vojtech

  • Darla Brown
    Darla Brown Overachievers

    Almost. The formula returns #NO MATCH even for those with a date in the cell.

    Darla Brown

    What you meditate on, you empower!

    Overachiever - Core Product Certified - Mobilizer - EAP

  • Darla Brown
    Darla Brown Overachievers

    UPDATE:

    I realized my unique identifier should not have been the property but the agency@row instead. I made that change:

    =INDEX({W9 Tracker Date Rec}, MATCH([Name of Agency]@row, {W9 Tracker Agency}, 0))

    But now it is returning a blank cell instead of the date.

    Darla Brown

    What you meditate on, you empower!

    Overachiever - Core Product Certified - Mobilizer - EAP

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Double check your source data. Find the very top/first row that matches for [Name of Agency] and that is the row your INDEX function is going to be pulling from.

  • Darla Brown
    Darla Brown Overachievers

    On this particular sheet there is only one agency with this name, but I need to think about that for the future if there are more than one. Still not pulling in a date though.

    Thanks Paul.

    Darla Brown

    What you meditate on, you empower!

    Overachiever - Core Product Certified - Mobilizer - EAP

  • Darla Brown
    Darla Brown Overachievers
    Answer ✓

    UPDATE (FIXED):

    I did go back and check the source data. In between the time I had initiated this discussion and began the work, someone had entered a duplicate entry. This did cause the blank cell. Once I deleted the duplicate, the date pulled in.

    To prepare for any future duplications with multiple properties using same agencies, I made a combo formula column: =[Property #]@row + [Agency Name (Must match W9)]@row. Then I used that column in my INDEX formula: =INDEX({W9 Tracker Date Rec}, MATCH("7308" + [Name of Agency]@row, {W9 Tracker combo}, 0)).

    This is working. Thanks for the help @Paul Newcome and @Vojtech Gajdos !

    Darla Brown

    What you meditate on, you empower!

    Overachiever - Core Product Certified - Mobilizer - EAP

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!