Google Sheets: how to find max value in column B, corresponding values in column A, and max among these

I have two numeric columns, A and B. I want to find the max value in column B, which will return multiple rows. Then I want to find the max value in column A from among these rows.

A   B
5  315
7  315
10  275


The function should return:

 7  315


It would be fine if this were a combination of two functions - one that finds the max value in B (315), and another that finds the corresponding max value in A (7). That's how I'm trying it right now using:

=index(A2:B10,match(max(B2:B10),B2:B10,0))


But this solution only finds the latest row that has column B's max value:

 5  315


Someone suggested I use the query function, but I couldn't get that to work either.

Suppose you have data in the columns A, B from row 2 to 4. Put MAX(B2:B4) somewhere (in this example, I put it in B6). Then for the corresponding max in A you can use the formula MAX(ARRAYFORMULA(IF(B2:B4 = $B$6,A2:A4))).

• That works! Also, see the method I figured out in my separate answer. Mar 18, 2019 at 21:21

I figured this out using the query function and referencing a specific cell.

First, as @dcolazin suggests, you put the max of column B in a cell (I chose B1):

=max(B2:B10)


Then you do this in cell A1 (notice the "&&" to reference a cell within your SQL query):

=query(A2:B10,"select max(A) where B >= "&B1&"")