Select max value from 9 cells, copy cell col heading to other cell
 

Hi,
I've selected the maximum value from 9 cells in a row using:
=IF(BA3="","",MAX(DQ3,DR3,DS3,DT3,DU3,DV3,DW3,DX3, DY3))
and placed that max value in cell DZ3.

I now want to auto-insert the column heading from above that max value cell
(DQ3 to DY3) into cell EA3 (Again if cell BA3="" (blank), I want "" returned

Can't use a nested IF formula as 9 IFs being tested (only 7 allowed in Excel
2003).

Can anyone help with a formula please?
Regards,
Steve.

Select max value from 9 cells, copy cell col heading to other cell
 

You could shorten your formula in DZ3 to:

=IF(BA3="","",MAX(DQ3:DY3))

Since your range is consecutive cells, you don't need to list each
individually.

To get the header, you can use an INDEX/MATCH formula:

=IF(BA3="","",INDEX(DQ2:DY2,1,MATCH(DZ3,DQ3:DY3,0) )

HTH
Elkar


"Struggling in Sheffield" wrote:

Hi,
I've selected the maximum value from 9 cells in a row using:
=IF(BA3="","",MAX(DQ3,DR3,DS3,DT3,DU3,DV3,DW3,DX3, DY3))
and placed that max value in cell DZ3.

I now want to auto-insert the column heading from above that max value cell
(DQ3 to DY3) into cell EA3 (Again if cell BA3="" (blank), I want "" returned

Can't use a nested IF formula as 9 IFs being tested (only 7 allowed in Excel
2003).

Can anyone help with a formula please?
Regards,
Steve.

Select max value from 9 cells, copy cell col heading to other cell
 

Here's one way:

1. Insert a row immediately below row 3
2. In Cell DQ4, put the formula '=DQ2' to copy the label below the value
3. In Cell DR3, put the formula '=DR2' to copy the label below the value
4. Repeat for all labels

5. In cell DZ3, put the following formula:
=IF(BA3="","",HLOOKUP(DZ3,DQ3:DY4,2,FALSE))

6. Hide row 4.

Hlookup will work, but the value you want must be in the second or
subsequent row of the table.

You can also shorten your MAX formula to:
=IF(BA3="","",MAX(DQ3:DY3))

Cheers!

--Dan


"Struggling in Sheffield" wrote:

Hi,
I've selected the maximum value from 9 cells in a row using:
=IF(BA3="","",MAX(DQ3,DR3,DS3,DT3,DU3,DV3,DW3,DX3, DY3))
and placed that max value in cell DZ3.

I now want to auto-insert the column heading from above that max value cell
(DQ3 to DY3) into cell EA3 (Again if cell BA3="" (blank), I want "" returned

Can't use a nested IF formula as 9 IFs being tested (only 7 allowed in Excel
2003).

Can anyone help with a formula please?
Regards,
Steve.

Select max value from 9 cells, copy cell col heading to other cell
 

Try in E3
=IF(BA3="","",OFFSET(DQ1,0,INDEX(DQ3:DY3,MAX(DQ3:D Y3))))

You can replace
=IF(BA3="","",MAX(DQ3,DR3,DS3,DT3,DU3,DV3,DW3,DX3, DY3))
with
=IF(BA3="","",MAX(DQ3:DY3)) since you are using ALL cells in the range...
easier to write and read

"Struggling in Sheffield" wrote:

Hi,
I've selected the maximum value from 9 cells in a row using:
=IF(BA3="","",MAX(DQ3,DR3,DS3,DT3,DU3,DV3,DW3,DX3, DY3))
and placed that max value in cell DZ3.

I now want to auto-insert the column heading from above that max value cell
(DQ3 to DY3) into cell EA3 (Again if cell BA3="" (blank), I want "" returned

Can't use a nested IF formula as 9 IFs being tested (only 7 allowed in Excel
2003).

Can anyone help with a formula please?
Regards,
Steve.

Select max value from 9 cells, copy cell col heading to other
 

Hi Elkar,
Very many thanks for that, worked a treat.
I only turn to the forum after several hours of trying (and failing!) myself.
Cheers,
Steve.

"Elkar" wrote:

You could shorten your formula in DZ3 to:

=IF(BA3="","",MAX(DQ3:DY3))

Since your range is consecutive cells, you don't need to list each
individually.

To get the header, you can use an INDEX/MATCH formula:

=IF(BA3="","",INDEX(DQ2:DY2,1,MATCH(DZ3,DQ3:DY3,0) )

HTH
Elkar


"Struggling in Sheffield" wrote:

Hi,
I've selected the maximum value from 9 cells in a row using:
=IF(BA3="","",MAX(DQ3,DR3,DS3,DT3,DU3,DV3,DW3,DX3, DY3))
and placed that max value in cell DZ3.

I now want to auto-insert the column heading from above that max value cell
(DQ3 to DY3) into cell EA3 (Again if cell BA3="" (blank), I want "" returned

Can't use a nested IF formula as 9 IFs being tested (only 7 allowed in Excel
2003).

Can anyone help with a formula please?
Regards,
Steve.

Select max value from 9 cells, copy cell col heading to other
 

Hi Dan,
Tried Elkar's idea first and that worked fine so problem sorted, but thanks
for trying to help, much appreciated.
Cheers,
Steve.

"Dan" wrote:

Here's one way:

1. Insert a row immediately below row 3
2. In Cell DQ4, put the formula '=DQ2' to copy the label below the value
3. In Cell DR3, put the formula '=DR2' to copy the label below the value
4. Repeat for all labels

5. In cell DZ3, put the following formula:
=IF(BA3="","",HLOOKUP(DZ3,DQ3:DY4,2,FALSE))

6. Hide row 4.

Hlookup will work, but the value you want must be in the second or
subsequent row of the table.

You can also shorten your MAX formula to:
=IF(BA3="","",MAX(DQ3:DY3))

Cheers!

--Dan


"Struggling in Sheffield" wrote:

Hi,
I've selected the maximum value from 9 cells in a row using:
=IF(BA3="","",MAX(DQ3,DR3,DS3,DT3,DU3,DV3,DW3,DX3, DY3))
and placed that max value in cell DZ3.

I now want to auto-insert the column heading from above that max value cell
(DQ3 to DY3) into cell EA3 (Again if cell BA3="" (blank), I want "" returned

Can't use a nested IF formula as 9 IFs being tested (only 7 allowed in Excel
2003).

Can anyone help with a formula please?
Regards,
Steve.

Select max value from 9 cells, copy cell col heading to other
 

Hi Sheeloo,
Tried Elkar's suggestion first and that worked fine, but thanks very much
for trying to help, appreciate it.
Cheers,
Steve.

"Sheeloo" wrote:

Try in E3
=IF(BA3="","",OFFSET(DQ1,0,INDEX(DQ3:DY3,MAX(DQ3:D Y3))))

You can replace
=IF(BA3="","",MAX(DQ3,DR3,DS3,DT3,DU3,DV3,DW3,DX3, DY3))
with
=IF(BA3="","",MAX(DQ3:DY3)) since you are using ALL cells in the range...
easier to write and read

"Struggling in Sheffield" wrote:

Hi,
I've selected the maximum value from 9 cells in a row using:
=IF(BA3="","",MAX(DQ3,DR3,DS3,DT3,DU3,DV3,DW3,DX3, DY3))
and placed that max value in cell DZ3.

I now want to auto-insert the column heading from above that max value cell
(DQ3 to DY3) into cell EA3 (Again if cell BA3="" (blank), I want "" returned

Can't use a nested IF formula as 9 IFs being tested (only 7 allowed in Excel
2003).

Can anyone help with a formula please?
Regards,
Steve.