How to find the address of the cell containing the max value in an Excel row, skipping every other cell in the row?

On each row, I would like to get the address of the cell with the largest value, skipping every other cell e.g. I want to only assess the values in columns A,C,E,H,J and ignore the values in columns B,D,F,I,K

I think I need something similar to this:

``````=CELL("address",INDEX(1:1,MATCH(MAX(1:1),1:1,0)))
``````

but skipping every other column however I can't get it to work

EDIT: What I want after identifying the max cell is to then a) ideally: display the cell's column header text (row 1) or b) at least display the column letter

-
Do you want the address in order to do something else or is that the end result? - if it's the former then getting an address is not normally the best way to go.... – barry houdini Jan 25 '13 at 16:31
....also....Do you have any header values in the alternate columns with common text or similar? If you do that's the simplest way to refer to the columns you need – barry houdini Jan 25 '13 at 16:38

Progress! I can get MAX to skip every other cell like this:

``````=MAX(A2,C2,E2)
``````

And to then display the max cell's heading text, this works:

``````=INDEX(\$A\$1:\$F\$1,1,MATCH(MAX(B2,D2,F2),A2:F2,0))
``````

This is not perfect though as although MAX skips every other cell as desired, the outer MATCH and INDEX functions do not, so if an odd column has a duplicate of the MAX value, it could still display the wrong heading. But its close enough for now!

-

This version would ensure that you only ever get the value from B1, D1 or F1, whatever the values in A2, C2 or E2

`=INDEX(CHOOSE({1,2,3},B\$1,D\$1,F\$1),MATCH(MAX(B2,D2,F2),CHOOSE({1,2,3},B2,D2,F2),0))`

Of course that would be a long formula if extended over a much larger range - what's the real range that you need to look at?

-