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

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.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"March" wrote:

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

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



"March" wrote:

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

March wrote...
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!A2:D2 and
fill down as far as needed.