Formula to get most recent status for a particular client

David Hilliard

I have two sheets:

History Sheet

For collecting status of various items for various clients via forms. All clients responses are stored in a single sheet. Each response is a row.

Summary Sheet

For displaying only key metrics for all clients. Each client is a row. The cells in question would have a formula that finds the most recent status for that category, for that date.

So: Client = Client, Metric = Metric, Date = MAX

I've learned how to use INDEX/MATCH from some other similar posts here, and I have the below formula working as intended, but it doesn't check for the client. I've been trying for a bit to figure out how to implement that, but that's where I'm stuck. It seems my answer may be MAX(COLLECT( within the MATCH function, but I can't get my head around it.

=INDEX({History - Client Satisfaction COL}, MATCH(MAX({History - DateCreated COL}), {History - DateCreated COL}))

Thanks!

David Hilliard

I figured this out, the below formula provides the behavior I'm looking for:

=INDEX({History - Client Satisfaction COL}, MATCH(MAX(COLLECT({History - DateCreated COL}, {History - Client COL}, Client@row)), {History - DateCreated COL}))

David Hilliard

I figured this out, the below formula provides the behavior I'm looking for:

=INDEX({History - Client Satisfaction COL}, MATCH(MAX(COLLECT({History - DateCreated COL}, {History - Client COL}, Client@row)), {History - DateCreated COL}))