5 Replies Latest reply on Jan 26, 2016 6:17 AM by renske.hercules.0

    LOOKUP using 2 datasets, including dates and strings

    renske.hercules.0

      Hi All,

       

      I am trying to create a calculated field where I want to lookup a string value from a certain column in Dataset 1 and show it in Dataset 2.

       

      This is how it should work:

       

      STEP 1:

      It should compare titles In Dataset 1 with Dataset 2. The titles are written differently in both datasets. Therefore I created a calculated field twice: LOWER(Dataset 1) and LOWER(Dataset 2) so it would match the titles correctly.

       

      STEP 2:

      It should look at the date shown in dataset 2 and compare it to the startdate and enddate of dataset 1 to check if it falls within those dates. So DATE (dataset1) >= STARTDATE (dataset 2) AND DATA (dataset1) <= ENDDATE (dataset 2).

       

      STEP 3:

      If all conditions are met, it should look at Series Number and show it in Dataset 2, If not it should show NULL or a string like "Not available".

       

      I attached dataset 1 and dataset 2 to this post and a workbook for anyone whos interested .

       

      Who is willing to help me??

       

      Thanks a lot!!!

       

      Best,

      Renske