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

JDen

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.

New Sheet.xlsx

New Sheet2.xlsx

Bassam Khalil

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 Khalil

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.

JDen

Hi @Bassam Khalil , I just added you as an Admin. Thanks for the help.

Bassam Khalil

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"), " / ")))

JDen

That worked brilliantly, thank you very much sir! I also learned a lot in the process and that is invaluable, greatly appreciated.

Bassam Khalil

@JDen

I am glad that you found the solution in my answer and I will be happy to help you at any time.