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
TocogroupAsked:
Who is Participating?
 
NBVCConnect With a Mentor Commented:
Try:

=LOOKUP(10^10,Manchester_RNL_Budget[@[January]:[December]],Manchester_RNL_Budget[[#Headers],[January]:[December]])
0
 
TocogroupAuthor Commented:
That's excellent.
Many thanks
Toco
0
 
TocogroupAuthor Commented:
I should have asked, what does the 10^10 do ?
0
 
NBVCCommented:
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[@[January]:[December]].  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.
0
 
TocogroupAuthor Commented:
very clever...nice one !
0
All Courses

From novice to tech pro — start learning today.