VLOOKUP dates that fall within a range

I created a sheet to populate inspection dates by cross referencing (VLOOKUP) the license plate number from the inspection record sheet. The tracking sheet needs to populate dates within a column that represents each month (assume current year).

I was able to return dates based on this formula:

=VLOOKUP([Primary Column]@row, {Plate and Date}, 2, false)

but how do I limit the returned values to only that fall within that month?

Thanks in advance, Yael.


Best Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    I would suggest an INDEX/COLLECT.

    =INDEX(COLLECT({Source Sheet Date Column}, {Source Sheet Date Column}, IFERROR(MONTH(@cell), 0) = 1, {Source Sheet Plate Number Column}, [Primary Column]@row), 1)


    This will restrict it to January dates. You can change the first 1 to any month number you want.


    If you wanted to restrict the year as well it would look something like this (for Jan 2021)…

    =INDEX(COLLECT({Source Sheet Date Column}, {Source Sheet Date Column}, AND(IFERROR(YEAR(@cell), 0) = 2021, IFERROR(MONTH(@cell), 0) = 1), {Source Sheet Plate Number Column}, [Primary Column]@row), 1)

  • Yael Claussen
    Yael Claussen ✭✭✭
    Answer ✓

    I got it. Thanks so much!

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!