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

✭✭✭✭

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.

Tags:

• ✭✭✭✭✭✭

Hi @JDen

=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.khalil2009@gmail.com

• ✭✭✭✭✭✭

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.

bassam.khalil2009@gmail.com

• ✭✭✭✭

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

• ✭✭✭✭✭✭

Hi @JDen

=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.khalil2009@gmail.com

• ✭✭✭✭

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

• ✭✭✭✭✭✭

@JDen