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

    LOOKUP using 2 datasets, including dates and strings


      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!!!