1

I had a formula that worked in Libreoffice that no longer works after transferring to Sheets, as it used the Aggregate formula. Someone else made it for me and I don't know exactly how to replicate it.

Basically what I need to do is find the largest value in one column, based on criteria in a second column, but return the text in a third column as the result.

I'll give an example.

A                B              C      
-----------------------------------
Apple            3              Q1
-----------------------------------
Orange           4              Q1
-----------------------------------
Pear             6              Q1
-----------------------------------
Banana           3              Q1
-----------------------------------
Apple            9              Q2

The formula I need would result in "Pear" based on the criteria that it is the highest number in B, because it only searched "Q1" in column C.

Basically, I want the highest "B" number but also limiting what I look for in "C", and displaying the result at the text in "A".

I had a really long Aggregate formula that worked wonderfully but, like I said, moving to Sheets doesn't allow it to be used. I'm sure there's a pretty simple way to do this but I'm not smart enough to find it.

Thanks for the help!

Edit: Here is the original formula:

=INDEX(A2:A,AGGREGATE(15,6,(ROW(A2:A)-ROW(A2)+1)/((C2:C="Q1")(B2:B=AGGREGATE(14,6,B2:B/(ISNUMBER(B2:B)(C2:C="Q1")),1))),1)))

Improve this question
1

2 Answers 2

Reset to default
0

=INDEX(SORT(FILTER(A1:C5,C1:C5="Q1"),2,0),1,1)

Explanation

Source Data

A B C D
1 Apple 3 Q1
2 Orange 4 Q1
3 Pear 6 Q1
4 Banana 3 Q1
5 Apple 9 Q2

Step 1

Filter the data for only 'Q1'

=FILTER(A1:C5,C1:C5="Q1")

Col 1 Col 2 Col 3
Apple 3 Q1
Orange 4 Q1
Pear 6 Q1
Banana 3 Q1

Step 2

SORT the array (filtered data) by Col 2 in descending order (max at the top)

=SORT(FILTER(A1:C5,C1:C5="Q1"),2,0)

Col 1 Col 2 Col 3
Pear 6 Q1
Orange 4 Q1
Apple 3 Q1
Banana 3 Q1

Step 3

INDEX the sorted array to get the value in Row 1, Column 1

=INDEX(SORT(FILTER(A1:C5,C1:C5="Q1"),2,0),1,1)

Pear                                             
Improve this answer
2
  • That is almost perfect! The only thing that isn't quite working is that when I apply it to the project I need it for (lots more data than the example) it will prioritize empty columns over the one (which there are some of those). I assume that's what the IsNumber part was for in the original (I seem to recall this happened before as well) but I'm not sure how/where to insert it into yours.
    – AniMarter
    Dec 21, 2022 at 23:34
  • 1
    It turns out there were spaces, not blanks, and it was searching the spaces and prioritizing those. Removing the spaces fixed it. If it has multiple of the same value, it will pick the first one that comes up, which is what my previous one did as well, so that's fine. Thank you so much for your help!
    – AniMarter
    Dec 21, 2022 at 23:34
0

Use filter(), like this:

=filter( A1:A, C1:C = "Q1", B1:B = max(filter(B1:B, C1:C = "Q1")) )

If you want just the first result, wrap the formula in single().

Improve this answer
3
  • Hi, thanks for the help. I copy-pasted your formula and it gives me a "No matches are found in FILTER evaluation" error for this example. It works without the "Max" part but obviously that's important to this.
    – AniMarter
    Dec 21, 2022 at 22:10
  • Neither one works unless I change the value of Pear to be 9 or greater. It seems it's getting messed up when the largest number in column B is not part of "Q1."
    – AniMarter
    Dec 21, 2022 at 22:22
  • Right, got it. Edited the answer.
    – doubleunary
    Dec 22, 2022 at 8:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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