Clever Geek Handbook

Excel - find value in two dimensions

I have a data table like this:

     a    b    c    d
1    1    2    3    4
2    5    6    7    8
3    9    10   11   12
4    13   14   15   16

And I want a formula that will find the maximum value (16) and return the row number (in this case 4). How to do it?

INDEX(a1:d1,MATCH(MAX(a1:h4),a1:h4,0),)

does not work: (

+3


source to share


5 answers


Sheet layout::
B1:E1

column
A2: A5

headers
B2:E5

: row headers : data

Array formula:

{MAX(IF(B2:E5=MAX(B2:E5);ROW(B2:E5)-1;""}


Since the question is first asked as "d", the corresponding array formula is below:

{=OFFSET(A1;MAX(IF(B2:E5=MAX(B2:E5);ROW(B2:E5)-1;""));0)}

Shift-Ctrl-Enter in the formula window to paste. The slanted brackets are inserted by Excel, not by the user.

+2


source


And one more humble opinion of the girl:



=ADDRESS(ROW(OFFSET(A1,MAX(IF(B2:E5=MAX(B2:E5),ROW(B2:E5)-1,"")),0)),COLUMN(OFFSET(A1,0,MAX(IF(B2:E5=MAX(B2:E5),COLUMN(B2:E5)-1,"")))),4)

- but entered as an ARRAY formula through Ctrl+Shift+Enter

, will return E5

(assuming areas @Jüri Ruut

), which is simply the address of the desired cell.

+2


source


Hope this screenshot is self-explanatory in itself?

enter image description here

+1


source


Ok, I would program a macro like this: Iterate over each line - find the maximum for that line. Store the value in an array and then calculate the maximum again.

Perhaps it will work with formulas too. Just calculate the maximum of each row in a separate column and THEN calculate the maximum of that column.

0


source


Try this array formula

=MIN(IF(A1:H4=MAX(A1:H4),ROW(A1:H4)))

confirmed CTRL+SHIFT+ENTER

if there are multiple occurrences of the MAX value in A1: H4, then the formula will give you the string first in which it occurs

0


source






More articles:


All Articles