VLOOKUP With a Month Date Match

RayF

Hi, I am trying to populate a column in Sheet 1"Obsolescence Amount" from data in sheet 2. But I need the month's to match. I tried the formula below in many different formats but failed.

Sheet 1

Sheet 2

Thank you!

Genevieve P.

Hi @RayF

Ah, my apologies! I missed a small detail. When we're looking for a MONTH within a date column, we actually need a statement to tell the formula to find the MONTH of each cell (or @cell) in the range and see if it = the MONTH we want, like so:

{Date Column}, MONTH(@cell) = MONTH([Date of Contract]@row)

However if we're using MONTH(@cell), we also want to wrap this in an IFERROR statement so it ignores blank cells, like so:

IFERROR(MONTH(@cell), 0)

Try this formula structure:

=INDEX(COLLECT({Obsolescence Column}, {Hino Dealer Column}, [Hino Dealer]@row, {Upload Date Column}, IFERROR(MONTH(@cell), 0) = MONTH([Date of Contract]@row)), 1)

The 1 at the end of the INDEX function tells the formula to pull back the first match. This means that if you have more than 1 row where the month is the same, it will take the top one in your sheet. Does that make sense?

Cheers,

Genevieve

Genevieve P.

Hi @RayF

Instead of combining VLOOKUP and MATCH, since you have two criteria you'll want to combine INDEX and COLLECT.

The structure of an INDEX(COLLECT is like so:

=INDEX(COLLECT({Column to Return}, {Column 1}, [1st Criteria]@row, {Column 2}, [2nd Criteria]@row), 1)

So in your case, something like this:

=INDEX(COLLECT({Obsolescence Column}, {Hino Dealer Column}, [Hino Dealer]@row, {Upload Date Column}, MONTH([Date of Contract]@row)), 1)

Let me know if this works for you!

Cheers,

Genevieve

RayF

Hi @Genevieve P.

Thank you for your response. I tried your suggestion but I am receiving an "#Invalid Value" error. I have tried everything to try and get this to work. My formula is below:

My other question is, will this work as other months are uploaded? I didn't know if the 1 at the end of the formula was looking for just January? This will be a column formula.

Thanks in advance for your help!

Genevieve P.

Hi @RayF

Ah, my apologies! I missed a small detail. When we're looking for a MONTH within a date column, we actually need a statement to tell the formula to find the MONTH of each cell (or @cell) in the range and see if it = the MONTH we want, like so:

{Date Column}, MONTH(@cell) = MONTH([Date of Contract]@row)

However if we're using MONTH(@cell), we also want to wrap this in an IFERROR statement so it ignores blank cells, like so:

IFERROR(MONTH(@cell), 0)

Try this formula structure:

=INDEX(COLLECT({Obsolescence Column}, {Hino Dealer Column}, [Hino Dealer]@row, {Upload Date Column}, IFERROR(MONTH(@cell), 0) = MONTH([Date of Contract]@row)), 1)

The 1 at the end of the INDEX function tells the formula to pull back the first match. This means that if you have more than 1 row where the month is the same, it will take the top one in your sheet. Does that make sense?

Cheers,

Genevieve

RayF

Hi @Genevieve P.

This worked perfectly! Thank you so much. I was struggling with this for three days.

Genevieve P.

No problem, I'm glad you got it working!