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












0












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










share|improve this question







New contributor




adriandahlin is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.







$endgroup$

















    0












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










    share|improve this question







    New contributor




    adriandahlin is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
    Check out our Code of Conduct.







    $endgroup$















      0












      0








      0





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










      share|improve this question







      New contributor




      adriandahlin is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
      Check out our Code of Conduct.







      $endgroup$




      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.







      google






      share|improve this question







      New contributor




      adriandahlin is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
      Check out our Code of Conduct.











      share|improve this question







      New contributor




      adriandahlin is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
      Check out our Code of Conduct.









      share|improve this question




      share|improve this question






      New contributor




      adriandahlin is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
      Check out our Code of Conduct.









      asked 2 days ago









      adriandahlinadriandahlin

      11




      11




      New contributor




      adriandahlin is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
      Check out our Code of Conduct.





      New contributor





      adriandahlin is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
      Check out our Code of Conduct.






      adriandahlin is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
      Check out our Code of Conduct.






















          1 Answer
          1






          active

          oldest

          votes


















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






          share|improve this answer








          New contributor




          dcolazin is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
          Check out our Code of Conduct.






          $endgroup$













            Your Answer





            StackExchange.ifUsing("editor", function () {
            return StackExchange.using("mathjaxEditing", function () {
            StackExchange.MarkdownEditor.creationCallbacks.add(function (editor, postfix) {
            StackExchange.mathjaxEditing.prepareWmdForMathJax(editor, postfix, [["$", "$"], ["\\(","\\)"]]);
            });
            });
            }, "mathjax-editing");

            StackExchange.ready(function() {
            var channelOptions = {
            tags: "".split(" "),
            id: "557"
            };
            initTagRenderer("".split(" "), "".split(" "), channelOptions);

            StackExchange.using("externalEditor", function() {
            // Have to fire editor after snippets, if snippets enabled
            if (StackExchange.settings.snippets.snippetsEnabled) {
            StackExchange.using("snippets", function() {
            createEditor();
            });
            }
            else {
            createEditor();
            }
            });

            function createEditor() {
            StackExchange.prepareEditor({
            heartbeatType: 'answer',
            autoActivateHeartbeat: false,
            convertImagesToLinks: false,
            noModals: true,
            showLowRepImageUploadWarning: true,
            reputationToPostImages: null,
            bindNavPrevention: true,
            postfix: "",
            imageUploader: {
            brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
            contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
            allowUrls: true
            },
            onDemand: true,
            discardSelector: ".discard-answer"
            ,immediatelyShowMarkdownHelp:true
            });


            }
            });






            adriandahlin is a new contributor. Be nice, and check out our Code of Conduct.










            draft saved

            draft discarded


















            StackExchange.ready(
            function () {
            StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdatascience.stackexchange.com%2fquestions%2f47368%2fgoogle-sheets-how-to-find-max-value-in-column-b-corresponding-values-in-column%23new-answer', 'question_page');
            }
            );

            Post as a guest















            Required, but never shown

























            1 Answer
            1






            active

            oldest

            votes








            1 Answer
            1






            active

            oldest

            votes









            active

            oldest

            votes






            active

            oldest

            votes









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






            share|improve this answer








            New contributor




            dcolazin is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
            Check out our Code of Conduct.






            $endgroup$


















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






              share|improve this answer








              New contributor




              dcolazin is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
              Check out our Code of Conduct.






              $endgroup$
















                0












                0








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






                share|improve this answer








                New contributor




                dcolazin is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
                Check out our Code of Conduct.






                $endgroup$



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







                share|improve this answer








                New contributor




                dcolazin is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
                Check out our Code of Conduct.









                share|improve this answer



                share|improve this answer






                New contributor




                dcolazin is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
                Check out our Code of Conduct.









                answered yesterday









                dcolazindcolazin

                111




                111




                New contributor




                dcolazin is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
                Check out our Code of Conduct.





                New contributor





                dcolazin is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
                Check out our Code of Conduct.






                dcolazin is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
                Check out our Code of Conduct.






















                    adriandahlin is a new contributor. Be nice, and check out our Code of Conduct.










                    draft saved

                    draft discarded


















                    adriandahlin is a new contributor. Be nice, and check out our Code of Conduct.













                    adriandahlin is a new contributor. Be nice, and check out our Code of Conduct.












                    adriandahlin is a new contributor. Be nice, and check out our Code of Conduct.
















                    Thanks for contributing an answer to Data Science Stack Exchange!


                    • Please be sure to answer the question. Provide details and share your research!

                    But avoid



                    • Asking for help, clarification, or responding to other answers.

                    • Making statements based on opinion; back them up with references or personal experience.


                    Use MathJax to format equations. MathJax reference.


                    To learn more, see our tips on writing great answers.




                    draft saved


                    draft discarded














                    StackExchange.ready(
                    function () {
                    StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdatascience.stackexchange.com%2fquestions%2f47368%2fgoogle-sheets-how-to-find-max-value-in-column-b-corresponding-values-in-column%23new-answer', 'question_page');
                    }
                    );

                    Post as a guest















                    Required, but never shown





















































                    Required, but never shown














                    Required, but never shown












                    Required, but never shown







                    Required, but never shown

































                    Required, but never shown














                    Required, but never shown












                    Required, but never shown







                    Required, but never shown







                    Popular posts from this blog

                    How to label and detect the document text images

                    Vallis Paradisi

                    Tabula Rosettana