Posted on 2012-09-17
Last Modified: 2012-09-17
I have a 6-column table and I'd like to output the column header for each row of the max-value column.    Ok, that may sound confusing... here's an example:

A     B     C     D     E     F           Best
98   97   88   99    76   81        D
92   93   91   92    95   97        F
98   89   88   97    96   98        A, F

Only challenge maybe the 3rd row where A and F have the same max value.  

How can this be accomplished (via HLookup)?

Question by:ExpExchHelp
    LVL 43

    Expert Comment

    by:Saqib Husain, Syed
    Hlookup will only return one. For more than on you probably have to go to VBA
    LVL 23

    Accepted Solution

    You can try this formula:  

    Assuming data is in A:F, then in G2 try:

    =SUBSTITUTE(TRIM(IF(A2=MAX(A2:F2),A$1,"")&" "&IF(B2=MAX(A2:F2),B$1,"")&" "&IF(C2=MAX(A2:F2),C$1,"")&" "&IF(D2=MAX(A2:F2),D$1,"")&" "&IF(E2=MAX(A2:F2),E$1,"")&" "&IF(F2=MAX(A2:F2),F$1,""))," ",", ")

    copied down.

    Author Comment


    That's the PERFECT solution!!!!

    Thousand thanks,

    Author Closing Comment

    Perfect Solution!!!!

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
    Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
    The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
    The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …

    734 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

    24 Experts available now in Live!

    Get 1:1 Help Now