nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Smartsheet Community

Connect with peers, share your expertise, and inspire what’s next in Smartsheet — from proven practices to practical how-to insights from fellow users and product experts.

Help! Need to match a cell between two dates with complex conditions

mcehovin

Hi, I have a complex formula that i cant seem to figure out.

I'll do my best to describe this below

In Sheet 1 i have a unique identifier whose condition changes from blue to green after a certain date

Unique identifier	Condition	StartDate	EndDate
123456	Blue	1/5/2025	31/10/2025
123456	Green	1/11/2025

I want to ensure that in Sheet 2 (table below) all line items for this unique identifier prior to the end date for blue have the condition blue and are green after this date

Unique identifier	Condition	StartDate	EndDate
123456	[?]	1/6/2025	15/8/2025
123456	[?]	16/8/2025	31/10/2025
123456	[?]	1/11/2025	15/1/2026
123456	[?]	15/1/2026	31/3/2026

What what would be the function

Find more posts tagged with

Sheets

Formulas

Accepted answers

Yulichka

You’re very welcome! I'm so glad the initial formula worked perfectly for your sheet.

To answer your question: yes, you absolutely just add it in, but how you add it depends on what you mean by "one condition or another."

Because the COLLECT function filters your data row-by-row, adding multiple criteria for the same column usually requires an OR statement.

Here are the two most common ways to expand this, depending on what your data looks like:

Scenario A: You want to match "Condition X" OR "Condition Y" from Sheet 1

If Sheet 1 now has multiple statuses that should count as a match (for example, you want to pull the row if the condition is "Blue" or "Cyan"), you wrap those choices in an OR statement inside the criteria section:

Excel

=INDEX(COLLECT({Sheet 1 Condition}, {Sheet 1 Identifier}, [Unique identifier]@row , {Sheet 1 Condition}, OR(@cell  = "Blue", @cell  = "Cyan"), {Sheet 1 StartDate}, @cell  <= [StartDate]@row , {Sheet 1 EndDate}, OR(@cell  >= [StartDate]@row , ISBLANK(@cell ))), 1)

Scenario B: You want to check a completely new column variable

If you mean you have a brand new column in Sheet 1 (let's say a {Sheet 1 Region} column) and it needs to match a [Region]@row in Sheet 2, you simply drop it in as a new criterion pair at the end of your COLLECT function:

Excel

=INDEX(COLLECT({Sheet 1 Condition}, {Sheet 1 Identifier}, [Unique identifier]@row , {Sheet 1 StartDate}, @cell  <= [StartDate]@row , {Sheet 1 EndDate}, OR(@cell  >= [StartDate]@row , ISBLANK(@cell )), {Sheet 1 Region}, [Region]@row ), 1)

Which of these two directions matches what you are trying to build out? If it's a bit of both, let me know the names of the new variables and we can map it out exactly!

Yulichka

You’re very welcome! I'm so glad the initial formula worked perfectly for your sheet.

To answer your question: yes, you absolutely just add it in, but how you add it depends on what you mean by "one condition or another."

Because the COLLECT function filters your data row-by-row, adding multiple criteria for the same column usually requires an OR statement.

Here are the two most common ways to expand this, depending on what your data looks like:

Scenario A: You want to match "Condition X" OR "Condition Y" from Sheet 1

Excel

=INDEX(COLLECT({Sheet 1 Condition}, {Sheet 1 Identifier}, [Unique identifier]@row , {Sheet 1 Condition}, OR(@cell  = "Blue", @cell  = "Cyan"), {Sheet 1 StartDate}, @cell  <= [StartDate]@row , {Sheet 1 EndDate}, OR(@cell  >= [StartDate]@row , ISBLANK(@cell ))), 1)

Scenario B: You want to check a completely new column variable

Excel

=INDEX(COLLECT({Sheet 1 Condition}, {Sheet 1 Identifier}, [Unique identifier]@row , {Sheet 1 StartDate}, @cell  <= [StartDate]@row , {Sheet 1 EndDate}, OR(@cell  >= [StartDate]@row , ISBLANK(@cell )), {Sheet 1 Region}, [Region]@row ), 1)

Which of these two directions matches what you are trying to build out? If it's a bit of both, let me know the names of the new variables and we can map it out exactly!

All comments

Yulichka

INDEX/COLLECT

mcehovin

i tried a couple of these but didn't work - not sure what i was doing wrong

Calc one:

=IFERROR(INDEX(COLLECT({Sheet 1 Condition}, {Sheet 2 Identifier}, Identifier@row , {Sheet 1 StartDate}, @cell <= [End Date]@row , {Sheet 1 EndDate}, OR(@cell > [End Date]@row , AND(ISBLANK(@cell ), COUNTIFS({Sheet 1 Identifier}, Identifier@row, {Sheet 1 StartDate}, @cell <= [End Date]@row ) > 0, [End Date]@row >= INDEX(COLLECT(Sheet 1 StartDate}, {Sheet 1 Identifier},
Identifier@row), COUNTIFS({Sheet 1 Identifier}, Identifier@row))))), 1),"")

Calc 2

=IFERROR(INDEX(COLLECT({Sheet
1 Condition}, {Sheet 1 Identifier}, @cell = Identifier@row , {Sheet 1 StartDate}, @cell <= [End Date]@row , {Sheet 1 End Date}, @cell > [End Date]@row ), 1), IF(AND(ISBLANK([End Date]@row ), [End Date]@row >= INDEX(COLLECT({Sheet 1 StartDate}, {Sheet 1 Identifier}, @cell = Identifier@row ), COUNTIF({Sheet 1
Identifier}, Identifier@row ))), INDEX(COLLECT({Sheet 1 Condition}, {Sheet 1 Identifier}, @cell = Identifier@row ), COUNTIF({Sheet 1 Identofer}, Identifier@row )), ""))

Yulichka

=INDEX(COLLECT({Sheet 1 Condition}, {Sheet 1 Identifier}, [Unique identifier]@row , {Sheet 1 StartDate}, @cell <= [StartDate]@row , {Sheet 1 EndDate}, OR(@cell >= [StartDate]@row , ISBLANK(@cell ))), 1)

Yulichka

{Sheet 1 Identifier}, [Unique identifier]@row: Matches the ID (e.g., 123456).
{Sheet 1 StartDate}, @cell <= [StartDate]@row : Ensures the Sheet 1 status had already started by the time this Sheet 2 row starts.
{Sheet 1 EndDate}, OR(@cell >= [StartDate]@row, ISBLANK(@cell)): This is the secret sauce. It ensures the Sheet 1 phase hadn't ended yet when the Sheet 2 row started, OR it handles the "Green" status where the End Date is completely blank.
INDEX(..., 1): Pulls the first matching result that fits all the criteria above.

Quick checklist for your cross-sheet references:

Make sure your braced references ({}) are exactly mapped to these specific columns in Sheet 1:

{Sheet 1 Condition} $\rightarrow$ The "Condition" column
{Sheet 1 Identifier} $\rightarrow$ The "Unique identifier" column
{Sheet 1 StartDate} $\rightarrow$ The "StartDate" column
{Sheet 1 EndDate} $\rightarrow$ The "EndDate" column

Yulichka

Hope that either one of these helps. If you do not mind sharing your sheet, I can give it a try.

mcehovin

Thanks Yulichka,

Can you just clarify the @cell - is this a specific cell in the reference sheet?

I think this is where i fell over on the other two?

mcehovin

Thanks Yulichka - Your formula has worked a treat.

I do need to add one more variable similar to "Sheet 1 Condition" where one condition or another in the same column is picked if other variables are met… do i just add this in?

Yulichka

Yes

Yulichka

You’re very welcome! I'm so glad the initial formula worked perfectly for your sheet.

To answer your question: yes, you absolutely just add it in, but how you add it depends on what you mean by "one condition or another."

Because the COLLECT function filters your data row-by-row, adding multiple criteria for the same column usually requires an OR statement.

Here are the two most common ways to expand this, depending on what your data looks like:

Scenario A: You want to match "Condition X" OR "Condition Y" from Sheet 1

Excel

=INDEX(COLLECT({Sheet 1 Condition}, {Sheet 1 Identifier}, [Unique identifier]@row , {Sheet 1 Condition}, OR(@cell  = "Blue", @cell  = "Cyan"), {Sheet 1 StartDate}, @cell  <= [StartDate]@row , {Sheet 1 EndDate}, OR(@cell  >= [StartDate]@row , ISBLANK(@cell ))), 1)

Scenario B: You want to check a completely new column variable

Excel

=INDEX(COLLECT({Sheet 1 Condition}, {Sheet 1 Identifier}, [Unique identifier]@row , {Sheet 1 StartDate}, @cell  <= [StartDate]@row , {Sheet 1 EndDate}, OR(@cell  >= [StartDate]@row , ISBLANK(@cell )), {Sheet 1 Region}, [Region]@row ), 1)

Which of these two directions matches what you are trying to build out? If it's a bit of both, let me know the names of the new variables and we can map it out exactly!

Yulichka

You’re very welcome! I'm so glad the initial formula worked perfectly for your sheet.

To answer your question: yes, you absolutely just add it in, but how you add it depends on what you mean by "one condition or another."

Because the COLLECT function filters your data row-by-row, adding multiple criteria for the same column usually requires an OR statement.

Here are the two most common ways to expand this, depending on what your data looks like:

Scenario A: You want to match "Condition X" OR "Condition Y" from Sheet 1

Excel

=INDEX(COLLECT({Sheet 1 Condition}, {Sheet 1 Identifier}, [Unique identifier]@row , {Sheet 1 Condition}, OR(@cell  = "Blue", @cell  = "Cyan"), {Sheet 1 StartDate}, @cell  <= [StartDate]@row , {Sheet 1 EndDate}, OR(@cell  >= [StartDate]@row , ISBLANK(@cell ))), 1)

Scenario B: You want to check a completely new column variable

Excel

=INDEX(COLLECT({Sheet 1 Condition}, {Sheet 1 Identifier}, [Unique identifier]@row , {Sheet 1 StartDate}, @cell  <= [StartDate]@row , {Sheet 1 EndDate}, OR(@cell  >= [StartDate]@row , ISBLANK(@cell )), {Sheet 1 Region}, [Region]@row ), 1)

Which of these two directions matches what you are trying to build out? If it's a bit of both, let me know the names of the new variables and we can map it out exactly!

mcehovin

I'll try the second function and let you know how i go

mcehovin

Hi Again,

Sorry, i didn't explain myself right.

Below is the same table approach as the initial questions but with the added Condition

In Sheet 1 i have a unique identifier whose condition changes from blue to green and condition 2 changes from Black to Red after a certain date

Unique identifier	Condition1	Condition2	StartDate	EndDate
123456	Blue	Black	1/5/2025	31/10/2025
123456	Green	Red	1/11/2025

I want to ensure that in Sheet 2 (table below) all line items for this unique identifier prior to the end date for blue have the condition blue and are green after this date, and the same for condition 2

Unique identifier	Condition1	Condition2	StartDate	EndDate
123456	[?]	[?]	1/6/2025	15/8/2025
123456	[?]	[?]	16/8/2025	31/10/2025
123456	[?]	[?]	1/11/2025	15/1/2026
123456	[?]	[?]	15/1/2026	31/3/2026

Is there a way to incorporate this additional query into the lovely function you gave me originally?

Yulichka

Maybe one other option…

Since Smartsheet handles range comparisons differently than Excel or Google Sheets, the most reliable approach is to use the INDEX(COLLECT(...)) pattern. This structure behaves exactly like a multi-criteria lookup, allowing you to match the Unique Identifier while simultaneously checking if your target date falls within the required window.

Assuming your Sheet 2 tracks rows based on a specific [StartDate]@row, you can use the formulas below to evaluate whether that date falls before, during, or after the date thresholds defined in Sheet 1.

The Smartsheet Formulas

Because the row tracking "Green" and "Red" in your Sheet 1 leaves the EndDate blank to signify it applies indefinitely into the future, we use an OR criteria. This allows the formula to match if Sheet 2's date is less than or equal to the EndDate OR if that EndDate is simply blank.

For Condition 1 (e.g., matching Blue vs. Green):

Excel

=IFERROR(INDEX(COLLECT({Sheet1 Condition1 Range}, {Sheet1 ID Range}, [Unique identifier]@row, {Sheet1 StartDate Range}, <=[StartDate]@row, {Sheet1 EndDate Range}, OR(@cell >= [StartDate]@row, ISBLANK(@cell))), 1), "")

For Condition 2 (e.g., matching Black vs. Red):

Excel

=IFERROR(INDEX(COLLECT({Sheet1 Condition2 Range}, {Sheet1 ID Range}, [Unique identifier]@row, {Sheet1 StartDate Range}, <=[StartDate]@row, {Sheet1 EndDate Range}, OR(@cell >= [StartDate]@row, ISBLANK(@cell))), 1), "")

Setting Up Your Cross-Sheet References

In Smartsheet, you cannot simply copy and paste curly bracket references ({ }) because they represent active links to external sheets. When writing or editing these formulas, click the "Reference Another Sheet" link in the formula help card and establish these links manually:

{Sheet1 Condition1 Range}: Select the Condition1 column in Sheet 1.
{Sheet1 Condition2 Range}: Select the Condition2 column in Sheet 1.
{Sheet1 ID Range}: Select the Unique identifier column in Sheet 1.
{Sheet1 StartDate Range}: Select the StartDate column in Sheet 1.
{Sheet1 EndDate Range}: Select the EndDate column in Sheet 1.

Why This Logic Resolves the Conditions Safely:

For a Sheet 2 row with StartDate 1/6/2025: The formula finds the row in Sheet 1 where the ID matches, the Sheet 1 StartDate is $\le$ 1/6/2025 (which matches 1/5/2025), and the Sheet 1 EndDate (31/10/2025) is $\ge$ 1/6/2025. It successfully pulls Blue / Black.
For a Sheet 2 row with StartDate 15/1/2026: The formula finds the second row in Sheet 1 where the StartDate is $\le$ 15/1/2026 (matching 1/11/2025), and since the EndDate is empty, ISBLANK(@cell) triggers true. It successfully pulls Green / Red.

Once you paste the formula into the first row of your column, right-click the cell and select Convert to Column Formula to apply it down the entire sheet automatically.

Paul Newcome

@Yulichka Please be sure to identify which AI you are using for your answers.

Yulichka

Thank you for letting me know. It was Gemini Pro AI being used.

mcehovin

Thanks Yulichka - these have worked perfeclty

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.