How do I return an Excel table column heading based on a table value ?

Posted on 2014-07-16
Last Modified: 2014-07-16
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
Question by:Tocogroup
    LVL 23

    Accepted Solution



    Author Closing Comment

    That's excellent.
    Many thanks

    Author Comment

    I should have asked, what does the 10^10 do ?
    LVL 23

    Expert Comment

    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.

    Author Comment

    very clever...nice one !

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Do You Know the 4 Main Threat Actor Types?

    Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

    Dealing with unintended Excel Active-X resizing quirks (VBA code simulates "self correction") David Miller (dlmille) Intro Not everyone is a fan of Active-X controls in spreadsheets (as opposed to the UserForm approach, the older Form controls …
    Introduction This Article is a follow-up to my Mappit! Addin Article (, it was inspired by an email posting I made to EUSPRIG (, I will briefly cover: 1) An overvie…
    The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
    This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now