IF Multiple values selected THEN VLOOKUP and return values

Is there a way to return values in a cell if multiple criteria is met? I would like to track the amount of time our Onboarding Coordinators spend on tasks for each Country. So when they create a contract in Austria it takes 60 minutes but for Croatia it takes 50 minutes.

For a contract to take 60 minutes, date fields within the Smartsheet must be populated: "Send Contract Drafts and Offer Approval for Review", "Receive All Approvals", "Complete & Send DocuSign Envelope", and "Docusign completed".


IF those fields have a date THEN the amount of time spent would be "60" for Austria, "50" for Croatia, etc.

Here is the formula I have, but it isn't working:

=IF(ISDATE([Send Contract Drafts and Offer Approval for Review]1, [Receive All Approvals]1, “Cell contains a date”, “Cell doesn’t contain a date”), VLOOKUP([Country - Site]1, {Copy of Time per OB Stage Range 2}, 3, false))

Can someone take a look at my formula and tell me what might be wrong?

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Can you explain your thinking behind this part?

    =IF(ISDATE([Send Contract Drafts and Offer Approval for Review]1, [Receive All Approvals]1, “Cell contains a date”, “Cell doesn’t contain a date”)


    I am having trouble following what you want from it, but this is where the issue is.

  • I am trying to capture when all these fields contain a date, then go ahead and lookup the value (true) but if they DONT return a date then don't return a value OR the value is 0 (false) until all fields have a date then it's the vlookup value.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    So...

    IF

    [Send Contract Drafts and Offer Approval for Review] IS a DATE

    AND

    [Receive All Approvals] IS a DATE

    AND

    [Complete & Send DocuSign Envelope] IS a DATE

    AND

    [Docusign completed] IS a DATE


    then run

    VLOOKUP([Country - Site]1, {Copy of Time per OB Stage Range 2}, 3, false)


    =IF(AND(ISDATE([Send Contract Drafts and Offer Approval for Review]@row), ISDATE([Receive All Approvals]@row), ISDATE([Complete & Send DocuSign Envelope]@row), ISDATE([Docusign completed]@row)), VLOOKUP([Country - Site]1, {Copy of Time per OB Stage Range 2}, 3, false))

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!