How to request a value from a column by filtering a range

user_2478606 0
Viewed 25

Having in column C country names and in range E:N population in different years I try to find the country with the max population ever (so in the range E2:N43) tried with queries below wihout success:

=QUERY(A1:N43,"select C WHERE '"&MAX(E2:N43)&"' IN '"&E2:N43)
=QUERY(A1:N43,"select C WHERE '"&MAX(E2:N43)&"' = '"&E2:N43&"' ")

What's wrong?

google-query-language flatten filter max min
1 Answers

min:

=FLATTEN(INDEX(SORT(SPLIT(FLATTEN(FILTER(E2:N&"×"&C2:C, C2:C<>"")), "×"), 1, 1), 1))

max:

=FLATTEN(INDEX(SORT(SPLIT(FLATTEN(FILTER(E2:N&"×"&C2:C, C2:C<>"")), "×"), 1, 0), 1))

enter image description here

user_56326290
Related