Multiple criteria with INDEX and MATCH / COLLECT with MAX Functions

Chitta

Hi,

I have the below columns in my Smartsheet and I would like to retrieve Finance column value if Quarter=2, Year=2021, Req Type=L and Max(Row ID).

I tried with below query but failed and I am not able to see how MATCH / COLLECT Function can be used. Any help would be greatly appreciated.

Thanks in advance,

~Chitta

Chitta

Small change in my query and it worked!

Working query:

=INDEX([Finance]:[Finance],MAX(COLLECT([Row ID]:[Row ID], [Req Type]:[Req Type], "L", Quarter:Quarter, 2, Year:Year, 2021)), 1)

Chitta

I tried below 2 queries, one works with returning row id and other one fails with "INCORRECT ARGUMENT SET" error:, I needed 2nd query to be used to fetch the Finance value.

Below query works with MAX Row ID returns but I want to store Finance field value in my summary sheet.

=MAX(COLLECT([Row ID]:[Row ID], [Req Type]:[Req Type], "L", Quarter:Quarter, 2, Year:Year, 2021))

Below query fails with "INCORRECT ARGUMENT SET" error

=INDEX(MAX(COLLECT([Finance]:[Finance], [Row ID]:[Row ID], [Req Type]:[Req Type], "L", Quarter:Quarter, 2, Year:Year, 2021)), 1)

~Chitta

Chitta

Small change in my query and it worked!

Working query:

=INDEX([Finance]:[Finance],MAX(COLLECT([Row ID]:[Row ID], [Req Type]:[Req Type], "L", Quarter:Quarter, 2, Year:Year, 2021)), 1)