CELL or ROW function in an array function?

[Dimitris254]

i'm scanning some ranges with an array to get the max value:

{=MAX(IF(A2:A12=J7; IF(B2:B12=L5 ;D2:D12)))}

but i don't want the max, i want to get the row where this max is.

however, if i put CELL or ROW, Excel doesn't accept it

is not possible at all?

 

[Marcelo Branco]

Try

=MIN(IF(A2:A12=J7;IF(B2:B12=L5;IF(D2:D12=MAX(IF(A2:A12=J7;IF(B2:B12=L5;D2:D12)));ROW(D2:D12)))))
Ctrl+Shift+Enter

Hope this helps

M.

 

[Dimitris254]

cheers

 

[Marcelo Branco]

cheers

M.