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?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

NBVCCommented:
Try:

=LOOKUP(10^10,Manchester_RNL_Budget[@[January]:[December]],Manchester_RNL_Budget[[#Headers],[January]:[December]])
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.