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.



Best Answer

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    edited 04/28/21 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

    PMP Certified

    [email protected]


    ☑️ 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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!