IF Multiple values selected THEN VLOOKUP and return values

Stephanie Bauder

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?

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.

Stephanie Bauder

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

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))