Wsrtjtyk

I have an excel sheet with following data

Name Time1 Number1 Time2 Number2 Time3 Number3 Time4 Number4 Time5 Number5



ABC   0.1    4      0.2    3     0.3    2       0.4    5      0.5   6

DEF   1.1    1      1.2    5     0.8    6       0.6    9      0.9   7

GHI   1.2    4      3.2    3     0.3    9       0.1    2      1.6   10

I want to find the minimum of Time1 to Time5 for each Name along with the corresponding Number. For time1 corresponding number is number1, for name2 is number2 and so on. for each row it should also which of the time it chooses. For example for the above data output should be like.

Name Time Number

ABC   0.1  4    Time1

DEF   0.6  9    Time4

GHI   0.1  2    Time4

here is sample

i will apply the formula in L for minimum time, corresponding number in M and which time in N.

You can use these formulae

Cell M2: =A2

Cell N2: =MIN(B2,D2,F2,H2,J2)

Cell O2: =INDEX(B2:K2,0,MATCH(N2,IF(LEFT($B$1:$K$1,4)="Time",B2:K2),0)+1)

Cell P2: =INDEX($B$1:$K$1,0,MATCH(N2,IF(LEFT($B$1:$K$1,4)="Time",B2:K2),0))

The formula in cell O2 and P2 is an array formula, you have to press Ctrl+Shift+Enter to use it.

Assuming your data starts at cell A1.
Using ";" as seperator (might have to be changed to "," depending on your settings)

Assuming the result is placed in the range (A8:D8), with A8 baring the name (in this example 'ABC') that serves as basis for the formulas:

Formula for the time of ABC (in cell B8):

=MIN(B2;D2;F2;H2;J2)

Formula for the corresponding number of ABC (in cell C8):

=INDIRECT(ADDRESS(MATCH(A8;A$2:A$4;0)+1;MATCH(B8;A2:K2;0)+1;4))

Formula for the remark of ABC (returns Time1) (in cell D8):

=INDIRECT(ADDRESS(MATCH(A8;A$2:A$4;0);MATCH(B8;A2:K2;0);4))

Drag down for the other Names