# Pulling a date if 2 cells match, and the date being pulled is within 7 days of a date column.

Options
✭✭

Looking for assistance on the formula below.

I'm pulling a date from a form sheet. The formula is working correctly up until the , AND function.

=INDEX(COLLECT({2023 Hours and Miles Form - Oil Change She Range 5}, {2023 Hours and Miles Form - Oil Change She Range 3}, [Hidden Zone:]@row, {2023 Hours and Miles Form - Oil Change She Range 2}, [Truck #]@row, AND(ISDATE(@cell), @cell <= {2023 Hours and Miles Form - Oil Change She Range 5}, @cell >= [Tuesday Date]@row - 7)), 1)

• ✭✭✭✭✭
Options

@Dakota What column are you trying to reference with @cell? I think you're missing the range of that column prior to the AND statement. Also, what are you trying to do with the `@cell <= {2023 Hours and Miles Form - Oil Change She Range 5}` piece? I don't think that will work since it's comparing a value to a range.

See below for the missing range (Range bold, criteria italic)

=INDEX(COLLECT({2023 Hours and Miles Form - Oil Change She Range 5}, {2023 Hours and Miles Form - Oil Change She Range 3}, [Hidden Zone:]@row, {2023 Hours and Miles Form - Oil Change She Range 2}, [Truck #]@row, <<Missing Range>>AND(ISDATE(@cell), @cell <= {2023 Hours and Miles Form - Oil Change She Range 5}, @cell >= [Tuesday Date]@row - 7)), 1)

• ✭✭✭✭✭
Options

@Dakota What column are you trying to reference with @cell? I think you're missing the range of that column prior to the AND statement. Also, what are you trying to do with the `@cell <= {2023 Hours and Miles Form - Oil Change She Range 5}` piece? I don't think that will work since it's comparing a value to a range.

See below for the missing range (Range bold, criteria italic)

=INDEX(COLLECT({2023 Hours and Miles Form - Oil Change She Range 5}, {2023 Hours and Miles Form - Oil Change She Range 3}, [Hidden Zone:]@row, {2023 Hours and Miles Form - Oil Change She Range 2}, [Truck #]@row, <<Missing Range>>AND(ISDATE(@cell), @cell <= {2023 Hours and Miles Form - Oil Change She Range 5}, @cell >= [Tuesday Date]@row - 7)), 1)

• ✭✭
Options

It works... Thank you Sharkasits! The missing range is the same range I am trying to pull. It works... I didn't know where the correct placement was for the range.

=INDEX(COLLECT({2023 Hours and Miles Form - Oil Change She Range 5}, {2023 Hours and Miles Form - Oil Change Range 3}, [Hidden Zone:]@row, {2023 Hours and Miles Form - Oil Change She Range 2}, {2023 Hours and Miles Form - Oil Change She Range 5}, AND(ISDATE(@cell), @cell >= [Tuesday Date]@row - 7)), 1)

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!