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)

edited 12/09/19

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:

• ✭✭✭✭✭✭

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

• 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.

• ✭✭✭✭✭✭

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?

• ✭✭✭✭✭✭

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

Craig

This discussion has been closed.