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

  • Thread starter Thread starter Struggling in Sheffield
  • Start date Start date
S

Struggling in Sheffield

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
 
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
 
Try in E3
=IF(BA3="","",OFFSET(DQ1,0,INDEX(DQ3:DY3,MAX(DQ3:DY3))))

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
 
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.
 
Hi Dan,
Tried Elkar's idea first and that worked fine so problem sorted, but thanks
for trying to help, much appreciated.
Cheers,
Steve.
 
Hi Sheeloo,
Tried Elkar's suggestion first and that worked fine, but thanks very much
for trying to help, appreciate it.
Cheers,
Steve.
 
Back
Top