Need Help Excel Formula

[March]

I have the excel sheet like below (sheet1)

sheet1

A B C
1 QC1 QC2 QC3
2 10 15 9
3 21 19 25


I would like to have the result in Sheet2 show like below

sheet2

A B C D
1 max Sys min Sys
2 15 QC2 9 QC3
3 25 QC3 19 QC2


I don't know how to build formulas to get the result in sheet2.


Please give me suggestion.


Thanks,

March

 

[Luke M]

Note that all of this assumes that you don't care if there are multiple cells
with the same Min/Max, as formulas will just return Sys value associated with
first one.

A2:
=MAX('Sheet1'!2:2)
B2:
=INDEX('Sheet1'!A$1:C$1,1,MATCH(A2,'Sheet1'!A2:C2,0))
C2:
=MIN('Sheet1'!2:2)
D2:
=INDEX('Sheet1'!A$1:C$1,1,MATCH(C2,'Sheet1'!A2:C2,0))

Copy all 4 formulas downward as needed.

 

[Francis]

Hi try these formulas in Sheet2,
A2 =MAX(Sheet2!A2:C2)
B2
=INDEX(Sheet2!$A$1:$C$1,SUMPRODUCT((Sheet2!$A$2:$C$3=$A2)*COLUMN(Sheet2!$A$2:$C$3)))
C2 =MIN(Sheet2!A2:C2)
D2
=INDEX(Sheet2!$A$1:$C$1,SUMPRODUCT((Sheet2!$A$2:$C$3=$C2)*COLUMN(Sheet2!$A$2:$C$3)))

change the sheet's and range references to yours
--
Hope this is helpful

Pls click the Yes button below if this post provide answer you have asked

Thank You

cheers, francis

Am not a greek but an ordinary user trying to assist another

 

[Harlan Grove]

I have the excel sheet like below (sheet1)

sheet1

          A         B         C
1       QC1     QC2     QC3
2         10        15        9
3         21        19       25

I would like to have the result in Sheet2 show like below

sheet2

          A          B         C       D
1      max      Sys      min    Sys
2        15        QC2     9       QC3
3        25        QC3    19      QC2

....

So going through the table in Sheet1 row by row?

Sheet2!A2:
=MAX(Sheet1!A2:C2)

Sheet2!B2:
=INDEX(Sheet1!$A$1:$C$1,MATCH(A2,Sheet1!$A2:$C2,0))

Sheet2!C2:
=MIN(Sheet1!A2:C2)

Copy Sheet2!B2 and paste into Sheet2!D2. Then select Sheet2!A22 and
fill down as far as needed.