Excel Functions

[LNS]

hi,

i have a worksheet as similar to the one below and i am trying to calculate
the minimum function and retrieve the corresponding column title:

A1 B1 C1 D1 E1 F1 G1
A2 COUNTRY A B C MIN OPERATOR
A3 UK 24 91 38 24 ??
A4 USA 36 65 39 36 ??
A5 INDIA 54 76 22 22 ??

What function can i use to find that for UK minimum is 24 and operator will
be A???

please assist!

 

[Sheeloo]

You can use this in E2 and copy down
=INDEX($C$1:$E$1,MATCH(MIN(C2:E2),C2:E2,0))
without having a separate column for MIN

or this in F2 with MIN in E2
=INDEX($C$1:$E$1,MATCH(F2,C2:E2,0))

 

[LNS]

thanks i tried both functions with and without the need of a separate column
for MIN but only function with MIn worked, the other just shows the min value
and not the title.
also could you please assist on which function to use to calculate the 2nd
lowest,3rd lowest (and vice versa 2nd highest, 3rd highest) without having to
sort and arrange data in ascending/descending order.

 

[LNS]

yes, my apologies, both functions work fine!!!
thanks for assistance but plz assist with the other query as to how to
calculate 2nd & 3rd min/max???

 

[Sheeloo]

Try
=RANK(A1,$A$1:$A$6,0)
this will give you the rank of A1 in A1:A6, rank 1 for highest

=RANK(A1,$A$1:$A$6,1) will give you rank 1 for the smallest number

 

[LNS]

No, what i meant is when i want to find minimum in F3, then i use the function
=MIN(C3:E3) and for maximum i use =MAX(C3:E3), but is there any possible
function that i can use to calculate 2nd lowest/highest and 3rd
lowest/highest?

 

[Sheeloo]

You an combine Index and Match just like you did for MIN/MAX

For numbers in A1:A6
enter this in B1 and copy to B6

=RANK(A1,$A$1:$A$6,0)

and this in C1
=INDEX($A$1:$A$6,MATCH(3,$B$1:$B$6,0))
will give you the third highest number