apurk45 said:
Aladin
My apologies your formula works great. I fat fingered some incorrect cell #.
...
Even though your formula works perfect – I am lost as to understanding how it works. Would you mind explaining logic of your combabulation?...
We can break this up in 2 cells to gain more efficiency...
Instead of the following in H9...
=IF(N(K8)*(M8="IJK")*ISNUMBER(MATCH(MAX(ABS(F8:F10)),ABS(F8:F10),0)),INDEX(G8:G10,MATCH(MAX(ABS(F8:F10)),ABS(F8:F10),0)),"")
in I9 control+shift+enter:
=MATCH(MAX(ABS(F8:F10)),ABS(F8:F10),0)
then in H9 just enter:
=IF(N(K8)*(M8="IJK")*ISNUMBER(I9),INDEX(G8:G10,I9),"")
Now explaining my "confabulation" becomes much easier:
The formula in I9, if successful, establishes the position of the (first instance of the) MAX value in F8:F10. The presence of ABS applied to a multicell range requires a formula that operates on arrays...
The IF formula in H9 has a condition (actually: subconditions strung together with *, which behaves like boolean AND)...
N(K8) gives 0 if K8 houses either a real 0 or text like "", otherwise returns the non-zero number that K8 houses.
The M8="IJK" bit either returns TRUE or FALSE.
The ISNUMBER(I9) returns TRUE if the MATCH formula in I9 returns a position indicating (non-zero) number, otherwise FALSE when the MATCH formula returns #N/A.
So (a) 0 or Non-Zero multiplied with (b) TRUE or FALSE multipled with (c) TRUE or FALSE is bound to return either a 0 or a non-zero number. Excel interprets a 0 as FALSE and a non-zero number as TRUE. A non-zero number as result of IF's condition leads to the evaluation of the
INDEX(G8:G10,I9)
which fetches the value from G8:G10 at pos I9. A 0 as result of IF's condition, leads to "".