Dashboard based on Todays Date - From a table

Question

I have a list of students with responsibilities and I would like to create a dashboard in Google Sheets with information about the current month.

As an example:

then in September 2020, I want the dashboard to show:

... and also update automatically to Jose and Gina once we are in Oct 2020

Here is the demo sheet: https://docs.google.com/spreadsheets/d/113ZAUJgbzgtjEGai9LkM22h-AEyaPymxUHMnu-RLYEw/edit?usp=sharing

Thanks for any help!

Answer 1

EDIT

As very correctly noticed in the comments, there is a hasty, common, still unforgivable mistake in my original formula.

The correct formula is

=QUERY(Responsibilities!A2:C,"select B, C where month(A)="& month(A2)-1&"")

Please do notice that in order to get the current month one needs to subtract 1 month.

WHY?
Because in a query formula, when expressing months in numbers January corresponds to 0
So the order of the months is:

  January       0  
  February      1  
  March         2  
  April         3  
  May           4  
  June          5  
  July          6  
  August        7  
  September     8  
  October       9  
  November     10  
  December     11  

---

Original answer:

You would need 2 formulas in your dashboard sheet

Formula 1
You would first need to put today's date in a cell and refer to that cell to get the current month. That formula is:

=TODAY()

Formula 2
You can now use the following formula to get your results

=QUERY(Responsibilities!A2:C,"select B, C where month(A)="& month(A2)&"")

Functions used:

• QUERY
• TODAY

Answer 2

I found a way to do it myself. Only took 4 hours :(

=Index(Responsibilities!$B$2:$B$7, MATCH(DATEVALUE(CONCATENATE(MONTH(today()),"/1/",Year(today()))),Responsibilities!A2:A7,0),1)