Search code examples
filtermaxminflattengoogle-query-language

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

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?

Solution

  • 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