Lookup a column of values with the largest sum

Pille

New Member
Joined
Jun 6, 2017
Messages
15
Hi there,
Hope this post finds you well.
I need to lookup cells from one of four columns, and place them in a new column. The lookup columns are p/l columns and I'm looking for the column with the max sum. So if column b has the max sum I want all the cells from that column to show up in column x.

Kind Regards

Per
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
If I understand your question well, maybe like this.


Book1
ABCDEF
1result
21164916
383218
41159611
Blad9
Cell Formulas
RangeFormula
F2=MAX(A2:D2)
 
Upvote 0
I understood the request differently than Oeldere...

Row\Col
A​
B​
C​
D​
E​
F​
G​
1​
X1X2X3X4
2​
2​
1
16
4
9
X2
3​
8
3
2
1
16​
4​
11
5
9
6
3​
5​
5​
6​

In G1 control+shift+enter, not just enter:

=MATCH(MAX(MMULT(TRANSPOSE(A2:D4),ROW(A2:D4)^0)),MMULT(TRANSPOSE(A2:D4),ROW(A2:D4)^0),0)

In G2 just enter:

=INDEX(A1:D1,G1)

In G3 just enter and copy down:

=IFERROR(INDEX($A$2:$D$4,ROWS($G$3:G3),$G$1),"")
 
Upvote 0
Aladin,
I'm using this in a table importing csv files.
Is there a workaround so I can get this function on the same row?
As for now I'm missing out the last number in the table.

Kind regards
/Per
 
Upvote 0
Aladin,
I'm using this in a table importing csv files.
Is there a workaround so I can get this function on the same row?
As for now I'm missing out the last number in the table.

Kind regards
/Per

Care to specify what you mean according to the lay-out of post #3?
 
Upvote 0
G5 prints B4. The offset is not really a problem for looks. But in my table G5 will not print when I refresh.

Not over here. Are you having this problem with the set up of post #3 as is or with your own data for which you implemented the set up?
 
Upvote 0
I only implemented the setup to my sheet of imported data.
I'll build #3 and see if I'll get the same problem.
 
Upvote 0
Not over here. Are you having this problem with the set up of post #3 as is or with your own data for which you implemented the set up?
Ok checked and it works making a table with the above #3 including the empty cells.
I'm using the query editor to import my data. I can manually add a row to that table, but when I hit refresh the new last row will be deleted.

/Per
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,949
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top