how can find location of value in a square using excel-CodePudding

i have tried to find location of max value written between B4 and F11.

using =MATCH(MAX(B4:F11),B4:F11,0) returns n/a.

CodePudding user response：

For the cell address:

=LET(ζ,B4:F11,BASE(MIN(IF(ζ=MAX(ζ),DECIMAL(ADDRESS(ROW(ζ),COLUMN(ζ),4),36))),36))

CodePudding user response：

You can use MATCH only with one column directly. If you have multicolumns and want to find row number in given table you can use this:

=MATCH(1,MMULT(--(B4:F11=MAX(B4:F11)),TRANSPOSE(COLUMN(B4:F11)^0)),0)

CodePudding user response：

It is not clear from the question how to understand "location" (row, column, both, absolute or relative location) and in which format to return the information requested. Here, is an approach that returns the absolute row and column location:

=LET(x, B2:E5, xi, B2, LOC, LAMBDA(y,offset, offset   XMATCH(1, 
 BYROW(y, LAMBDA(a, MAX(N(a=MAX(y))))))), HSTACK(LOC(x, ROW(xi)-1), 
  LOC(TRANSPOSE(x), COLUMN(xi)-1)))

It uses a user LAMBDA function LOC to avoid the repetition of a similar process for the row to get the column. If you need just relative location within the array, then the second argument (offset) is not needed, so it can be simplified as follow:

=LET(x, B2:E5, LOC, LAMBDA(y, XMATCH(1, BYROW(y, LAMBDA(a, MAX(N(a=MAX(y))))))),
 HSTACK(LOC(x), LOC(TRANSPOSE(x))))

For just relative row location it would be enough:

=XMATCH(1, BYROW(B2:E5, LAMBDA(a, MAX(N(a=MAX(B2:E5))))))

Here is the output: