Welcome to the Smartsheet Forum Archives


The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

Lookup if date is between 2 dates, then return an associated value (same sheet)

Emma S
Emma S
edited 12/09/19 in Archived 2017 Posts

Hello:)

 

Is there a way to lookup a date, determine if it falls between 2 dates (fiscal year quarters), and then return the corresponding value?

 

ie: 

Date: 1/29/2016

Fiscal Year Q1: 1/1/2016 ~ 3/31/2016

Fiscal Year Q2: 4/1~June 31/2016

etc

 

So, the formula would check to see if {date} (1/29) is between 1/1 and 3/31, or between 4/1 and 6/31, etc. Once it finds what it is between, it returns the corresponding value (in this case, Fiscal Year Q1).

 

Possible in Smartsheet?

 

Thank you!

Emma

Tags:

Comments

  • J. Craig Williams
    J. Craig Williams ✭✭✭✭✭✭

    Emma,

     

    Yes.

     

    This shows the results

     

     

    This uses the LOOKUP function.

    Argument 1 (a) is coming from the cell at [Date]24 which is 1/29/16.

    Argument 2 (b) is the range (table) from the area above.

    Argument 3 (c) is telling the LOOKUP function to look at the 2nd column.

    The  last argument "true" tells lookup to look for a match that is less than or equal to what we are looking for (argument 1). Since 1/1 is less than 1/29, we have our match.

     

    Note that both 12/5/15 and 12/5/16 will return blank, which is OK since we didn't put them in our table.

     

    Hope this helps.


    Craig

     

     

    LookupDate.jpg

    LookupDate_MarkedUp.jpg

  • Would I have to add all the dates in the calendar?  That would be a huge list.  Is there any other way to indicate if a date falls beween X and Y then it belongs to a particular quarter.

  • J. Craig Williams
    J. Craig Williams ✭✭✭✭✭✭

    Helene,

     

    I am not sure I understand the question.

    For a particular year, you need 3 dates, but 4 is preferred so that you can add years too if you care about the difference between Q1 2016 and Q1 2017.

     

    Craig

     

     

     

     

     

  • Is the lookup function no longer available in SmartSheet?

  • J. Craig Williams
    J. Craig Williams ✭✭✭✭✭✭

    It is, but not documented. They changed the name and doc to VLOOKUP, but it still works.

    Craig

This discussion has been closed.