Excel: return name of column with highest value in a non-continuous array

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.

Zman

Posted 2014-01-17T18:00:20.403

Reputation: 13

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 – 2014-01-17T18:04:09.527

Are the column groups all the same? E.g. always A,E,I, etc.? – Julian Knight – 2014-01-17T18:09:12.590

Hi Julian Knight - yes, they are. – Zman – 2014-01-17T18:10:37.480

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 – 2014-01-17T18:11:37.143

Answers

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.

Julian Knight

Posted 2014-01-17T18:00:20.403

Reputation: 13 389

Thanks Julian. Never used PowerQuery, but I'll check it out. I'm running this on a server. – Zman – 2014-01-17T18:20:42.727

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 – 2014-01-17T18:23:14.570

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))

barry houdini

Posted 2014-01-17T18:00:20.403

Reputation: 10 434