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

  • 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

Juniad

Active Member
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 .
 

Attachments

  • Lookup value based on max value.xlsx
    9.5 KB · Views: 3
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
 

Attachments

  • LookupMaxValue.xlsx
    12.3 KB · Views: 6
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.
 
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.
 

Attachments

  • Lookup value based on max value (PB).xlsx
    31.7 KB · Views: 7
Back
Top