Look up a value using month of the date

Question

I have a table like below.

I want to have a cell where I can get the number depending on the current month.

For example, today is 31/3/2024. I would like the cell to be 4

Tomorrow when it is 1/4/2024. I would like it to automatically update to 3425

I don't want to use app script

Date	Number
3/2024	4
4/2024	3425
5/2024	6
6/2024	8
7/2024	456
8/2024	2343
9/2024	1234534
10/2024	546
11/2024	6
12/2024	234
1/2025	5634
2/2025	3454

Answer 1

So, if data in Column A is true date then use-

=FILTER(B2:B,A2:A>=EOMONTH(D2,-1)+1,A2:A<=EOMONTH(D2,0))
=FILTER(B2:B,A2:A>=EOMONTH(E2,-1)+1,A2:A<=EOMONTH(E2,0))

If you don't want to use cell reference then direct use TODAY() function inside formula so that it changes everyday.

=FILTER(B2:B,A2:A>=EOMONTH(TODAY(),-1)+1,A2:A<=EOMONTH(TODAY(),0))

And if the values are just text string in Column A then could use-

=FILTER(B2:B,MAP(A2:A,LAMBDA(x,IF(x="",,INDEX(SPLIT(x,"/"),1))))=MONTH(TODAY()))

Answer 2

After you put the list in your question in A:B, and set the target dates (or date strings) in D2:D, you may want to use the formula like what I wrote below:

=ARRAYFORMULA(
  IF(D2:D<>"", 
    FILTER(B2:B, 
      (YEAR(DATEVALUE(A2:A))=YEAR(DATEVALUE(D2:D))) 
      *
      (MONTH(DATEVALUE(A2:A))=MONTH(DATEVALUE(D2:D)))
    ),
  )
)

The keys are

1. DATEVALUE to cast strings into date values, so that you can use
2. YEAR and MONTH to extract the year and the month of both the list and the targets alternatively.

However, the solution provided above is correct only in the scenario where you do not have any target out of the range of list. If the scenario is in the case, you may want to use the revised version below:

=MAP(D2:D, 
  LAMBDA(target, 
    IF(target<>"", 
      IFERROR(FILTER(B2:B, 
        (YEAR(DATEVALUE(A2:A))=YEAR(DATEVALUE(target)))
          *
        (MONTH(DATEVALUE(A2:A))=MONTH(DATEVALUE(target)))
      ), ""),
 "")))

Demo is noted here: https://docs.google.com/spreadsheets/d/1HcL1ZFJ-26HU9lZVKJufE63cSDCOtVWYXOAqwnJPn40/edit#gid=0