1

I have looked up many answers to find the highest value in a column, and return the column name. However, every answer is dependent on using a continuous array and uses the INDEX function. This is not possible for me. I need to look at the values in 17 different columns, all 415 columns away from the next. It looks something like this:

A B C D E F G H I J K L M N O

1 3 5----7 1 2----8 2 4----6 0 9

I need to compare the values in columns A, E, I and M and have it tell me that since I has a value of 8, Column I is my answer. Then I need it to compare columns B, F, J, and N, and have it tell me column B is the highest column of that group. And finally, that of columns C, G, K and O, column O is the highest.

I am doing this for 100 million cells, 17 groups, of 415 columns each for 14,300 rows.

Improve this question
4
  • How many steps can you use? Can you find the max in each column and then find the max out of a group of columns?
    – Raystafarian
    Jan 17, 2014 at 18:04
  • Are the column groups all the same? E.g. always A,E,I, etc.?
    – Julian Knight
    Jan 17, 2014 at 18:09
  • Hi Julian Knight - yes, they are.
    – Zman
    Jan 17, 2014 at 18:10
  • Hi Raystafarian. I can easily find the max using the MAX function for each group. The max is 3 in one group. But I don't know what column 3 is in, and what the name of that column is.
    – Zman
    Jan 17, 2014 at 18:11

2 Answers 2

Reset to default
1

I would recommend installing Microsoft PowerQuery. Then using it to write your column comparisons, creating a new sheet in the process. You will need a significant chunk of RAM available though for a large query like that.

Improve this answer
2
  • Thanks Julian. Never used PowerQuery, but I'll check it out. I'm running this on a server.
    – Zman
    Jan 17, 2014 at 18:20
  • It's a bag of spanners! But it IS very powerful. It is also the latest cornerstone of Microsoft's Business Intelligence offering so it's worth knowing about. Oh, just a warning, you will need to set aside some "quality" time to get your head around it.
    – Julian Knight
    Jan 17, 2014 at 18:23
0

to find the maximum value in A2, E2, I2 and M2 and then the header in row 1 for the matching column you could use this formula

=INDEX(CHOOSE({1,2,3,4},A$1,E$1,I$1,M$1),MATCH(MAX(A2,E2,I2,M2),CHOOSE({1,2,3,4},A2,E2,I2,M2),0))

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .