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

Find more posts tagged with

Sheets

Formulas

Accepted answers

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

All comments

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.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.