VLOOKUP dates that fall within a range

Yael Claussen

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.

Paul Newcome

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

I got it. Thanks so much!

Paul Newcome

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

Thanks Paul! This worked beautifully. One more question: Is there a way I can have the return value for those that do not have an inspection date within that month to return as a blank cell rather than "#INVALID VALUE"?

Paul Newcome

You would use an IFERROR.

=IFERROR(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

This didn't work. I think we need another ( somewhere since we added a ) after the ""

Paul Newcome

Can you copy/paste the exact formula directly from the sheet to here?

Yael Claussen

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

this is returning the #INVALID VLAUE response

Paul Newcome

If you are getting that error from that formula, double check that the error is not present in any of the referenced ranges.

Yael Claussen

I got it. Thanks so much!

Paul Newcome

Happy to help. 👍️