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
-
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.
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!