Extract value based on actual month & year

[bigellow]

Hi all,

I have the following table with dates in A column, formatted as date, and values on B.

I would like the Cell C2 to show only the value that correspond to actual month and year, the date doesn't matter. For this month, it should show 10, for the next month ( from 1st of April) it should be shown 20 and next year in January to show 50 )

I tried following formula's, but it doesn't work

=VLOOKUP(AND(YEAR(TODAY());(MONTH(TODAY())));A2:B6;2;FALSE)

=VLOOKUP(MONTH(TODAY())&"/"&(YEAR(TODAY()));A2:B6;2;TRUE)

Thanks for the advice!

		Value for the current month and year
31/03/2024​	10​	10​
30/04/2024​	20​
31/05/2024​	30​
30/06/2024​	40​
31/01/2025​	50​

 

[shift-del]

Hi

=XLOOKUP(TEXT(TODAY(),"YYYYMM"),TEXT(A2:A6,"YYYYMM"),B2:B6)

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[bigellow]

Hi

=XLOOKUP(TEXT(TODAY(),"YYYYMM"),TEXT(A2:A6,"YYYYMM"),B2:B6)

Thanks a lot shift-del! It works.

 

[bigellow]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Thanks for the tip, done.