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 "".