CODEWITHSUNDEEP
excelmaxvlookupvba
KGHicks
KGHicks

Reputation: 3

Find max value from multiple columns and return cell value from adjacent column - Excel

I need help finding a way in excel to identify the max value from multiple columns and return the value in the adjacent column.

Using the table below as an example, let's say I want to simultaneously search columns A and C for the max value (89). Once the max value is identified, I would like excel to return the value in the adjacent column and cell (6) to column E. In reality, I need to find the max and adjacent value from eight columns.

    A     B     C     D     E
1   65    8     36    15    6
2   24    17    89    6
3   11    20    58    13        
4   42    5     29    11

I would greatly appreciate your help.

Thanks,

Upvotes: 0

Views: 1828

Answers (1)

Scott Craner
Scott Craner

Reputation: 152450

If the max number does not duplicate you can safely use this formula:

=INDEX($A:$D,AGGREGATE(15,6,ROW($A$1:$D$4)/((MOD(COLUMN(A1:D4),2)=1)*($A$1:$D$4=AGGREGATE(14,6,$A$1:$D$4/(MOD(COLUMN(A1:D4),2)=1),1))),1),AGGREGATE(15,6,COLUMN($A$1:$D$4)/((MOD(COLUMN(A1:D4),2)=1)*($A$1:$D$4=AGGREGATE(14,6,$A$1:$D$4/(MOD(COLUMN(A1:D4),2)=1),1))),1)+1)

enter image description here

Upvotes: 2

Related Questions