Find 3 Largest Values Addresses

{=CELL("address",INDEX(A1:A10,MATCH(LARGE(A1:A10,{1;2;3}),A1:A10,0)))}

Select 3 cells with your mouse, then enter the formula as an array (Push Ctrl, Shift and Enter simultaneously) Add more cells for more numbers.

or

{=CELL("address",INDEX(A1:A10,MATCH(LARGE(A1:A10,ROW(INDIRECT("1:3"))),A1:A10,0)))}

Select 3 cells with your mouse, then enter the formula as an array (Push Ctrl, Shift and Enter simultaneously) Add more cells for more numbers.


You can do this with criteria too.

{=CELL("address",INDEX(B1:B10,MATCH(LARGE(IF(A1:A10=A1,B1:B10,""),{1;2;3}),B1:B10,0)))}

Adjust A1 to suit as in A1:A10=A1 Select 3 cells with your mouse, then enter the formula as an array (Push Ctrl, Shift and Enter simultaneously) Add more cells for more numbers.

or

{=CELL("address",INDEX(B1:B10,MATCH(LARGE(IF(A1:A10=A1,B1:B10,""),ROW(INDIRECT("1:3"))),B1:B10,0)))}

Adjust A1 to suit as in A1:A10=A1 Select 3 cells with your mouse, then enter the formula as an array (Push Ctrl, Shift and Enter simultaneously) Add more cells for more numbers.



© Copyright andrewsexceltips.net All Rights Reserved.