Vlookup person on other sheet, return reference if a date falls between 2 other dates - V2
Good morning,
This is a follow up question on one I asked yesterday and which I closed before realizing I needed some more help: https://community.smartsheet.com/discussion/comment/289238#Comment_289238
@Bassam Khalil helped me brilliantly with the below formula but there is another variable I hadn't taken into account.
=JOIN(COLLECT({New Sheet2 Range 1(Contract Reference)}, {New Sheet2 Range 2 (Name)}, Name@row, {New Sheet2 Range 3 (Contract Effective Date)}, <[Date of Mission]@row, {New Sheet2 Range 4 (Contract Expiration Date)}, >[Date of Mission]@row))
PROBLEM: Some people have 2 contracts running at the same time, one for a specific type of Mission (A) and another for another type of Mission (B). The issue with the above formula is that it pulls the Contract reference of both contracts running at the same time for those that are in that situation.
QUESTION: Is there a way to have SmartSheet determine, lookup mission type and mission date in Sheet 2 and if you see a YES, lookup Contract Reference for that person? See below screen shots and Excel files to play around with.
Best Answer
-
Hi @JDen
Please check the following formula, i added in your shared sheet.
=IF([Type of Mission]@row = "A", JOIN(COLLECT({New Sheet2 Range 1}, {New Sheet2 Range 2}, Name@row, {New Sheet2 Range 3}, <[Date of Mission]@row, {New Sheet2 Range 4}, >[Date of Mission]@row, {Mission A}, "Yes"), " / "), IF([Type of Mission]@row = "B", JOIN(COLLECT({New Sheet2 Range 1}, {New Sheet2 Range 2}, Name@row, {New Sheet2 Range 3}, <[Date of Mission]@row, {New Sheet2 Range 4}, >[Date of Mission]@row, {Mission B}, "Yes"), " / ")))
bassam.khalil2009@gmail.com
☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"
Answers
-
Hi @JDen
Hope you are fine, could you please share me as an admin on both sheets so i can rectify the formula, and please don't forget to vote for me in my posts 😃 you will help me with your vote.
bassam.khalil2009@gmail.com
☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"
-
Hi @Bassam Khalil , I just added you as an Admin. Thanks for the help.
-
Hi @JDen
Please check the following formula, i added in your shared sheet.
=IF([Type of Mission]@row = "A", JOIN(COLLECT({New Sheet2 Range 1}, {New Sheet2 Range 2}, Name@row, {New Sheet2 Range 3}, <[Date of Mission]@row, {New Sheet2 Range 4}, >[Date of Mission]@row, {Mission A}, "Yes"), " / "), IF([Type of Mission]@row = "B", JOIN(COLLECT({New Sheet2 Range 1}, {New Sheet2 Range 2}, Name@row, {New Sheet2 Range 3}, <[Date of Mission]@row, {New Sheet2 Range 4}, >[Date of Mission]@row, {Mission B}, "Yes"), " / ")))
bassam.khalil2009@gmail.com
☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"
-
That worked brilliantly, thank you very much sir! I also learned a lot in the process and that is invaluable, greatly appreciated.
-
@JDen
I am glad that you found the solution in my answer and I will be happy to help you at any time.
bassam.khalil2009@gmail.com
☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 305 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!