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) and if i have two or three same values also I need the location
is it possible to do that?

thanks

excel

3 Replies

    • 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