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.

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.

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.

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.

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.

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.

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.