Forum Discussion

nofalhassan's avatar
nofalhassan
Copper Contributor
Jan 29, 2020
Solved

cell location inside array

hi, everybody i am working on a table array which contain many value i used the max formula to determinate the maximum value now i need to know the location of this value (the row and the column) a...
excel
nofalhassan's avatar
nofalhassan
Copper Contributor
Jan 29, 2020
(@Sergei Baklan) thank you so much
  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Jan 29, 2020

    nofalhassan , you are welcome.

    I missed the point you could have few same numbers within array, if so above formula doesn't work.

    That could be like this

    and we'd like to find position within the range of the value in cell G3. Which could be MAX(data).

    Let put in column H sequential numbers. When row position(s) as in I3

    =IF(LARGE((data=$G$3)*ROW(data),$H3),LARGE((data=$G$3)*ROW(data),$H3)-ROW($B$1),"")

    column position

    =IFERROR(MATCH($G$3,INDEX(data,$I3,0),0),"")

    and address is

    =IFERROR(ADDRESS(I3+ROW($B$1),J3+COLUMN($B$1)-1),"")

    Drag I3:K3 down till empty cells appear.

     

Resources