CODEWITHSUNDEEP
google-sheetsspreadsheetformulas
Kelvin Chong
Kelvin Chong

Reputation: 230

Query or Filter the Highest Value with Conditions

so I have this thing here below. I have been breaking my head for the past 2 days trying to figure it out.

Tada

On a separate page, I want to be able to know THE DATE of someone's best performance (Fronts, Spiffs, Apps separately) so I've tried the following

=offset(index('Junkyard 2'!K2:K1999,(MATCH(MAX('Junkyard 2'!K2:K1999),'Junkyard 2'!K2:K1999,0))),0,-9)

That gets me the date with the highest number but it is not necessarily from that person

So I've tried 

=FILTER('Junkyard 2'!B3:B2000,'Junkyard 2'!C3:C2000=B4,LARGE('Junkyard 2'!K3:K2000,1))

And it gives me this

FILTER has mismatched range sizes. Expected row count: 1998, column count: 1. Actual row count: 1, column count: 1.

So now I've tried query but I'm shit at query to be honest, don't understand much. 

=QUERY('Junkyard 2'!B3:O2000,"select B, Max(K) group by B"  )

This gives me the maximum of each day..again, not neccesarily done by the guy.

In a jist, I want to be able get the Day with Highest Number of Fronts/spiffs/apps that a guy I choose have done. It's so annoying.

Please help

Upvotes: 2

Views: 14026

Answers (1)

Ed Nelson
Ed Nelson

Reputation: 10259

I suggest that on a separate sheet, in cell A2 create a Data Validation dropdown of the names on Junkyard 2 column B. Then on the new sheet in B2 enter:

=QUERY('Junkyard 2'!A2:O2005, "Select A, K where B = '" & A2 &"' ORDER BY K  DESC LIMIT 1")

It will return the date and the max value from column K for the person selected in the Data Validation dropdown.

Upvotes: 3

Related Questions