Retrieve the cell address for the largest number in a range

[sandeepkayal]

I have following number values in cells A2, A4, A6, A8, A10 - 10, 12,
8, 6, 16. I need to find the cell address of the max value in the
range.

So the formula, in this example, should return me A4. I cannot use
MATCH, because A3 can also contain a value like 12.

Thanks,
Sandeep

 

[Max]

One way to evaluate it for the alternating range, ie to disregard
what's in A3, A5, A7, etc.

Put in the formula bar for say, B1, then array-enter the formula by
pressing CTRL+SHIFT+ENTER [CSE] (instead of just pressing ENTER):

="A"&MATCH(MAX(IF(MOD(ROW(A2:A10),2)=0,A2:A10)),IF(MOD(ROW(A2:A10),2)=0,A2:A10),0)+1

Correctly array-entered, Excel will wrap curly braces: { } around the
formula in B1. Visually check that these braces are inserted by Excel
within the formula bar after you array enter. If you don't see it, then
the formula has not been correctly array-entered and you'd get wrong
results. If so, click inside the formula bar, and try the CSE again.

Note that in the event of any ties in the max value within the
alternating range: A2, A4, A6, A8, A10, the expression will return the
cell ref of the "highest" cell with the max value. Eg if both A4 and A8
contain the same max value, then B1 returns: A4.