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

Dakota

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)

sharkasits

@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)

sharkasits

@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)

Dakota

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)