Vlookup person on other sheet, return reference if a date falls between 2 other dates

JDen

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

Bassam Khalil

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 Khalil

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

JDen

Hi Bassam,

Many thanks for looking into this for me. It worked perfectly!!!

JDen

Bassam Khalil

@JDen

You are welcome and I will be happy to help you any time.