Lookup Corresponding Value based on max values in multiple tables

[Juniad]

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 .

 

[bosco_yip]

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

 

[Peter Bartholomew]

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

 

[Juniad]

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.

 

[Peter Bartholomew]

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.