1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Lookup Corresponding Value based on max values in multiple tables

Discussion in 'Ask an Excel Question' started by Juniad, Mar 13, 2019.

  1. Juniad

    Juniad New Member

    Messages:
    11
    Hello sir,
    i have 5 different tables from where i want to lookup corresponding value based on max value in all tables. plz help me out sample sheet attached .

    Attached Files:

  2. bosco_yip

    bosco_yip Excel Ninja

    Messages:
    2,191
    To lookup corresponding value based on max value in all tables.

    1] In C10, enter formula :

    =INDEX(B3:S7,MATCH(A10,A3:A7,0),MATCH(MAX(INDEX(B3:S7,MATCH(A10,A3:A7,0),0)),INDEX(B3:S7,MATCH(A10,A3:A7,0),0),0)*COLUMN(A1:R1)+1)

    2] Click A10 dropdown list to select other Lookup value for testing purpose.

    Regards
    Bosco

    Attached Files:

    Thomas Kuriakose and Juniad like this.
  3. Peter Bartholomew

    Peter Bartholomew Well-Known Member

    Messages:
    709
    This is an answer.
    I wouldn't pretend for a moment that it is the answer.

    Attached Files:

    Thomas Kuriakose and Juniad like this.
  4. Juniad

    Juniad New Member

    Messages:
    11
    Thanks Bosco, But this was an example i have data in google sheets in different workbooks. i wanna lookup some corresponding values in different columns based on max lookup result.
  5. Peter Bartholomew

    Peter Bartholomew Well-Known Member

    Messages:
    709
    Out of curiosity, are the tables identical in size and do they have identical first columns? My feeling is that I would switch to Power Query to combine and filter data from multiple workbooks and combine it.

    Just to add to the mystification I have added a Power Query approach to append the tables before extracting the Name associated with the maximum value using a simple formula. I could also have done the filtering in PQ.

    Attached Files:

    Thomas Kuriakose likes this.

Share This Page