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

Emma S

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

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

 

 

Helene Delgado

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

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

 

 

 

 

 

Tlittle27

Is the lookup function no longer available in SmartSheet?

J. Craig Williams

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

Craig