Lookup a column of values with the largest sum

[Pille]

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

 

[Pille]

Sorry, correction. #16 should read A2:K1098. Otherwise the same until #20. Stupid me, type error.
/

 

[Aladin Akyurek]

No, A is just counting rows.
/Per

Is this row counting done by your query or some formula in A?

 

[Pille]

Query

 

[Aladin Akyurek]

Query

Define Lrow in Formulas | Name Manager as referring to:

=MATCH(REPT("z",255),data!$B:$B)

Adjust the sheet name if your data lands in a different sheet.

Define Nums in Formulas | Name Manager as referring to:

=data!$D$2:INDEX(data!$K:$K,Lrow)

Define Header in the Name Manager as referring to:

=data!$D$1:$K$1
__________________________________

data (contains the data to process)

Row\Col	A​	B​	C​	D​	E​	F​	G​	H​	I​	J​	K​
1​	row count	textfield-1	textfield-2	X1	X2	X3	X4	X5	X6	X7	X8
2​	1​	jad	damon	1​	16​	4​	9​
3​	2​	vad	nate	8​	3​	2​	1​
4​	3​	nad	joe	11​	5​	9​	6​
5​	4​	wad	john	6​		20​
6​	5​	qad	jon			15​

<tbody>
</tbody>

processing

Row\Col	A​
1​	3​
2​	5​
3​	X3
4​	4​
5​	2​
6​	9​
7​	20​
8​	15​
9​
10​
11​
12​

<tbody>
</tbody>

In A1 of processing control+shift+enter, not just enter:

=MATCH(MAX(MMULT(TRANSPOSE(Nums)+0,ROW(Nums)^0)),MMULT(TRANSPOSE(Nums)+0,ROW(Nums)^0),0)

In A2 of processing just enter:

=COUNT(INDEX(Nums,0,A1))

In A3 of processing just enter:

=INDEX(Header,A1)

In A4 of processing just enter and copy way down:

=IF(ROWS($A$4:A4)>$A$2,"",INDEX(Nums,ROWS($A$4:A4),$A$1))