Thanks:  0
Likes:  0

Thread: Lookup a column of values with the largest sum

1. Lookup a column of values with the largest sum

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

2. Re: Lookup a column of values with the largest sum

If I understand your question well, maybe like this.

ABCDEF
1result
21164916
383218
41159611

Worksheet Formulas
CellFormula
F2=MAX(A2:D2)

3. Re: Lookup a column of values with the largest sum

I understood the request differently than Oeldere...

 Row\Col A B C D E F G 1 X1 X2 X3 X4 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),"")

4. Re: Lookup a column of values with the largest sum

Hey, thanks a lot guys!

Kind regards
/Per

5. Re: Lookup a column of values with the largest sum

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

6. Re: Lookup a column of values with the largest sum

Originally Posted by Pille
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?

7. Re: Lookup a column of values with the largest sum

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

8. Re: Lookup a column of values with the largest sum

Originally Posted by Pille
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?

9. Re: Lookup a column of values with the largest sum

I only implemented the setup to my sheet of imported data.
I'll build #3 and see if I'll get the same problem.

10. Re: Lookup a column of values with the largest sum

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

User Tag List

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•