Compare dates, if date is most recent, then return adjacent cell data

Question

I'm working on a Google Sheets document for tracking covid-19 cases reported in nursing homes, institutions, etc. I'm heavily employing formulas across my sheets to minimize the number of ways my coworkers can accidentally screw up the numbers. I've made a copy of the pertinent sheet here so you can take a look.

I've run into a problem, though. So, on one sheet, we have all records of articles that mention statewide case/death numbers. We want to continue to keep track of those, but also be able to easily retrieve/display the most recently reported numbers for any given state. Essentially what I'm trying to do is:

1. Delimit search to only rows where column A contains a specific state (AL, FL, GA, etc)
2. Of those rows, compare the date values in column B, identify the most recent one
3. Once the most recent entry for a given state is found, then output the value of column E (or D, or C, doesn't matter, I assume that changing that part will be straightforward enough)

I got as far as =MAXIFS(B3:B100, A3:A100,"CT") but I'm not sure syntactically what to tell it next, or if I'm going about this the right way.

Update: We have gotten closer with =query(A1:E,"select A, C where A = 'CO' and B = date '"&TEXT(DATEVALUE(max(B2:B)),"yyyy-mm-dd")&"'",1) and =query(A1:E,"select A, C, D, E where A = 'CO' and B = date '"&TEXT(DATEVALUE(max(B2:B)),"yyyy-mm-dd")&"'",1), but in practice this only returns rows with the absolute most recent date, rather than the most recent date relative to which state's dataset you're looking at.

Need to be able to display the relative most recent entry per state.

UPDATE UPDATE: I figured it out! (sheet name added because I'm referencing it from a different sheet)

=query('Statewide Reported'!A1:E,"select A, B, C, D where A = 'NJ' and B = date '"&TEXT(maxifs('Statewide Reported'!B3:B100, 'Statewide Reported'!A3:A100, "NJ"),"yyyy-mm-dd")&"'",1)

Thanks so much for pointing me in the right direction!!

Answer 1

You could use query() to select the number of deaths for CO matching the most recent date using this formula:

=query(A1:E,"select A, C where A = 'CO' and B = date '"&TEXT(DATEVALUE(max(B2:B)),"yyyy-mm-dd")&"'",1)

If you want deaths, cases, and total affected you can use:

=query(A1:E,"select A, C, D, E where A = 'CO' and B = date '"&TEXT(DATEVALUE(max(B2:B)),"yyyy-mm-dd")&"'",1)

Update:

This will find the max date for a specific State (CO):

=query($A$1:$E,"select A, C, D, E where A = 'CO' and B = date '"&TEXT(maxifs($B$2:$B,$A$2:$A,"CO"), "yyyy-mm-dd")&"'",1)

Or if you want to reference a State name in a cell (H1) you can use this formula:

=query($A$1:$E,"select A, C, D, E where A = '"& $H$1 &"' and B = date '"&TEXT(maxifs($B$2:$B,$A$2:$A,$H$1), "yyyy-mm-dd")&"'",1)