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.
|