SOLVED

cell location inside array

Copper Contributor

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

3 Replies
best response confirmed by nofalhassan (Copper Contributor)
Solution
(@Sergei Baklan) thank you so much

@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

image.png

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.

 

1 best response

Accepted Solutions
best response confirmed by nofalhassan (Copper Contributor)