I m using Excel 2002. My data looks like this:

a b c d
1 yellow Green Brown pink
2 16 12 14 15

I have used =max and =large formuli in another sheet (same workbook) to
display data in ascending order in a different table.

I also want to 'label' the data but can't work it out i.e. labelling 16
'yellow' using/adapting the above formuli. Also, in a seperate cell I have
looked at/tried: =If(a2b2,"yellow",if(a2c2,"yellow",if(a2d2,"yel low")))

Which works fine provided yellow is always the greatest value but get an
error when I include the other columns in the same formula i.e. after
if(a2d2,"yellow",if(b12a2,"green" etc etc.

Can anyone help resolve this? Thanks.

I am not sure this is what you need, but we can find the max with MAX(); then
we locate the column with MATCH(); then we find the color with OFFSET():

=OFFSET(A1,0,MATCH(MAX(2:2),2:2,0)-1)

--
Gary''s Student - gsnu200786


"MNF" wrote:

I m using Excel 2002. My data looks like this:

a b c d
1 yellow Green Brown pink
2 16 12 14 15

I have used =max and =large formuli in another sheet (same workbook) to
display data in ascending order in a different table.

I also want to 'label' the data but can't work it out i.e. labelling 16
'yellow' using/adapting the above formuli. Also, in a seperate cell I have
looked at/tried: =If(a2b2,"yellow",if(a2c2,"yellow",if(a2d2,"yel low")))

Which works fine provided yellow is always the greatest value but get an
error when I include the other columns in the same formula i.e. after
if(a2d2,"yellow",if(b12a2,"green" etc etc.

Can anyone help resolve this? Thanks.

I'm assuming you want something like this:

12...Green
14...Brown
15...Pink
16...Yellow

Enter this formula in G1:

=SMALL(A$2:D$2,ROWS(G$1:G1))

Enter this formula in H1:

=INDEX(A$1:D$1,MATCH(G1,A$2:D$2,0))

Select both G1 and H1 and copy down to G4:H4

If there are any duplicate numbers this will not work properly and a
different approach will be needed.

--
Biff
Microsoft Excel MVP


"MNF" wrote in message
...
I m using Excel 2002. My data looks like this:

a b c d
1 yellow Green Brown pink
2 16 12 14 15

I have used =max and =large formuli in another sheet (same workbook) to
display data in ascending order in a different table.

I also want to 'label' the data but can't work it out i.e. labelling 16
'yellow' using/adapting the above formuli. Also, in a seperate cell I have
looked at/tried: =If(a2b2,"yellow",if(a2c2,"yellow",if(a2d2,"yel low")))

Which works fine provided yellow is always the greatest value but get an
error when I include the other columns in the same formula i.e. after
if(a2d2,"yellow",if(b12a2,"green" etc etc.

Can anyone help resolve this? Thanks.

Thanks very much. Your suggestion has sorted it out.

"T. Valko" wrote:

I'm assuming you want something like this:

12...Green
14...Brown
15...Pink
16...Yellow

Enter this formula in G1:

=SMALL(A$2:D$2,ROWS(G$1:G1))

Enter this formula in H1:

=INDEX(A$1:D$1,MATCH(G1,A$2:D$2,0))

Select both G1 and H1 and copy down to G4:H4

If there are any duplicate numbers this will not work properly and a
different approach will be needed.

--
Biff
Microsoft Excel MVP


"MNF" wrote in message
...
I m using Excel 2002. My data looks like this:

a b c d
1 yellow Green Brown pink
2 16 12 14 15

I have used =max and =large formuli in another sheet (same workbook) to
display data in ascending order in a different table.

I also want to 'label' the data but can't work it out i.e. labelling 16
'yellow' using/adapting the above formuli. Also, in a seperate cell I have
looked at/tried: =If(a2b2,"yellow",if(a2c2,"yellow",if(a2d2,"yel low")))

Which works fine provided yellow is always the greatest value but get an
error when I include the other columns in the same formula i.e. after
if(a2d2,"yellow",if(b12a2,"green" etc etc.

Can anyone help resolve this? Thanks.

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"MNF" wrote in message
...
Thanks very much. Your suggestion has sorted it out.

"T. Valko" wrote:

I'm assuming you want something like this:

12...Green
14...Brown
15...Pink
16...Yellow

Enter this formula in G1:

=SMALL(A$2:D$2,ROWS(G$1:G1))

Enter this formula in H1:

=INDEX(A$1:D$1,MATCH(G1,A$2:D$2,0))

Select both G1 and H1 and copy down to G4:H4

If there are any duplicate numbers this will not work properly and a
different approach will be needed.

--
Biff
Microsoft Excel MVP


"MNF" wrote in message
...
I m using Excel 2002. My data looks like this:

a b c d
1 yellow Green Brown pink
2 16 12 14 15

I have used =max and =large formuli in another sheet (same workbook) to
display data in ascending order in a different table.

I also want to 'label' the data but can't work it out i.e. labelling 16
'yellow' using/adapting the above formuli. Also, in a seperate cell I
have
looked at/tried:
=If(a2b2,"yellow",if(a2c2,"yellow",if(a2d2,"yel low")))

Which works fine provided yellow is always the greatest value but get
an
error when I include the other columns in the same formula i.e. after
if(a2d2,"yellow",if(b12a2,"green" etc etc.

Can anyone help resolve this? Thanks.