Vlookup person on other sheet, return reference if a date falls between 2 other dates
Hi all,
I need help with the following:
I need to find the contract reference of a person from Sheet 1 in Sheet 2, based on the name of the person and when the mission took place. As you can see below, James had a mission on the 16th of April, so I need the contract reference that corresponds to that date, which would be contract SSA-0009766. Is there a way to do this? I've been working on some formulas but I'm not getting anywhere.
I would really appreciate the help.
Thanks in advance.
Regards,
Julien
Best Answer
-
Hi @JDen
Hope you are fine, please try the following formula and convert it to column format formula:
=JOIN(COLLECT({Contract Reference}, {Names}, Name@row, {Contract Effective Date}, <[Date Of Mission]@row, {Contract Expiration Date}, >[Date Of Mission]@row))
the following screenshots show the results:
Sheet 1
Sheet 2
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, please try the following formula and convert it to column format formula:
=JOIN(COLLECT({Contract Reference}, {Names}, Name@row, {Contract Effective Date}, <[Date Of Mission]@row, {Contract Expiration Date}, >[Date Of Mission]@row))
the following screenshots show the results:
Sheet 1
Sheet 2
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,
Many thanks for looking into this for me. It worked perfectly!!!
-
-
@JDen
You are welcome and I will be happy to help you 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
- Customer Resources
- 66.2K Get Help
- 431 Global Discussions
- 152 Industry Talk
- 490 Announcements
- 5.2K Ideas & Feature Requests
- 85 Brandfolder
- 155 Just for fun
- 74 Community Job Board
- 501 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 306 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!