Tocogroup
asked on
How do I return an Excel table column heading based on a table value ?
Hi All,
I have an Excel 2010 table which consists of some Client details and the 12 months of the year as column headings. For each Client row there is a sales amount in ONE of the January to December columns.
I want to populate a new column (called Month) depending on which Jan to Dec column has the sales amount. So, if a sales figure of £5,600 is in the column headed 'April', then I want the Month column to be populated with the text item "April".
Is there a more efficient way of calculating this than using a large nested IF formula testing each cell in the Jan to Dec columns ?
I've attached a cut-down version of the sheet for clarity.
Thanks in anticipation
Toco
ExampleClientSheet.xlsx
I have an Excel 2010 table which consists of some Client details and the 12 months of the year as column headings. For each Client row there is a sales amount in ONE of the January to December columns.
I want to populate a new column (called Month) depending on which Jan to Dec column has the sales amount. So, if a sales figure of £5,600 is in the column headed 'April', then I want the Month column to be populated with the text item "April".
Is there a more efficient way of calculating this than using a large nested IF formula testing each cell in the Jan to Dec columns ?
I've attached a cut-down version of the sheet for clarity.
Thanks in anticipation
Toco
ExampleClientSheet.xlsx
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I should have asked, what does the 10^10 do ?
It's 10 to the power of 10 (i.e. a very large number).
Lookup() looks for that number in the range Manchester_RNL_Budget[@[Ja nuary]:[De cember]]. If it doesn't find it (which it won't), then the function is designed to return the last value that is less than or equal to the lookup value. This will be your single value in each row.
Lookup() looks for that number in the range Manchester_RNL_Budget[@[Ja
ASKER
very clever...nice one !
ASKER
Many thanks
Toco