1
$\begingroup$

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.

Improve this question
$\endgroup$

2 Answers 2

Reset to default
0
$\begingroup$

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))).

Improve this answer
$\endgroup$
1
  • $\begingroup$ That works! Also, see the method I figured out in my separate answer. $\endgroup$
    – adriandahlin
    Mar 18, 2019 at 21:21
0
$\begingroup$

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&"")
Improve this answer
$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.